Activerecord Join Table for Legacy Database

ActiveRecord Join table for legacy Database

I believe you can be slightly more elegant than Alvaro's answer using options to has_and_belongs_to_many, though his answer is perfectly fine and will result in fairly identical functionality for any clients of your class.

class TvShow < ActiveRecord::Base

  set_table_name "tvshow"
  set_primary_key "idShow"
  has_and_belong_to_many :episodes, 
                         :join_table => "tvshowlinkepisode", 
                         :foreign_key => "idShow",
                         :association_foreign_key => "idEpisode"

end

class Episode < ActiveRecord::Base
  set_table_name "episode"
  set_primary_key "idEpisode"
  has_and_belongs_to_many :tv_shows,
                          :join_table => "tvshowlinkepisode",
                          :foreign_key => "idEpisode",
                          :association_foreign_key => "idShow"
end

Note that the :foreign_key option specifies which column is the id for the class on "this side" of the link, while :association_foreign_key specifies the column that is the id for the class on the "other side" of the link.

Contrast this with Alvaro's answer, this pattern should avoid instantiation of any unnecessary objects to represent the link.

ActiveRecord Join table for legacy Database

I believe you can be slightly more elegant than Alvaro's answer using options to has_and_belongs_to_many, though his answer is perfectly fine and will result in fairly identical functionality for any clients of your class.

class TvShow < ActiveRecord::Base

  set_table_name "tvshow"
  set_primary_key "idShow"
  has_and_belong_to_many :episodes, 
                         :join_table => "tvshowlinkepisode", 
                         :foreign_key => "idShow",
                         :association_foreign_key => "idEpisode"

end

class Episode < ActiveRecord::Base
  set_table_name "episode"
  set_primary_key "idEpisode"
  has_and_belongs_to_many :tv_shows,
                          :join_table => "tvshowlinkepisode",
                          :foreign_key => "idEpisode",
                          :association_foreign_key => "idShow"
end

Note that the :foreign_key option specifies which column is the id for the class on "this side" of the link, while :association_foreign_key specifies the column that is the id for the class on the "other side" of the link.

Contrast this with Alvaro's answer, this pattern should avoid instantiation of any unnecessary objects to represent the link.

Using join table in a one to many association for legacy tables

That's a perfectly fine approach as far as I can see. Even if the model you were trying to connect to was a part of your current Rails app, having an explicit join table has many advantages (like being able to - either right away or later on - add more columns to it in order to add and capture more data).

I myself am working on something similar, but the other database I am touching is not a legacy, but quite new, except that I too only want to connect to it in a read only fashion, and so I make that similar join table. Nothing wrong with that!

Using ActiveRecord classes with legacy tables

Sorry to answer my own post, but I guess I didn't explain it very well. In case someone else comes along after this, what I needed to do was issue a call to the "Frt.reset_column_information" method. I was revisiting this issue, and just dumped all the methods on the ActiveRecord class, and found that one lurking in the list.

Non-normalized association with legacy tables in Rails and ActiveRecord

I don't know if you can do that with ActiveRecord, but if you want any solution to this problem, you can define a customer method on subscription model:

class Subscription < Activerecord::Base
   has_and_belongs_to_many :customers, :join_table => "subscribes",
      :foreign_key => "subscription_id",
      :association_foreign_key => "customer_id"

   def customer
      self.customers.first
   end
end

Complex joins with legacy tables in Rails

Create a ContractClient Join Model to correspond to the t15_contracts_clients table. This specifies the table name and uses belongs_to associations for the 2 foreign keys:

class ContractClient < ActiveRecord::Base
  set_table_name 't15_contracts_clients'
  belongs_to :client, :foreign_key => :id_client
  belongs_to :contract, :foreign_key => :id_contract
end

Specify the table and primary key for Client:

class Client < ActiveRecord::Base
  set_table_name 't20_clients'
  set_primary_key 'id_client'
end

Use a belongs_to :contract_client on Invoice and then a has_one :through to associate the client through the ContractClient:

class Invoice < ActiveRecord::Base
  set_table_name 't10_invoices'
  set_primary_key 'id_invoice'
  belongs_to :contract_client, :foreign_key => :id_contract
  has_one :client, :through => :contract_client
end

Invoice.find(:first).client should then behave as expected.

Rails ActiveRecord: legacy table without primary key shows nil for result?

Try to also set the primary key:

class FeatureAttribute < ActiveRecord::Base
  set_table_name 'feature_attribute'
  set_primary_key 'feature_id'

  belongs_to :feature
end

UPDATE

I think your problem lies anywhere else. I just tested and ActiveRecords works fine with tables without a primary key:

For a simple table:

class CreateThings < ActiveRecord::Migration
  def change
    create_table :things, :id => false do |t|
      t.string :name

      t.timestamps
    end
  end
end

in console:

Loading development environment (Rails 3.1.1)
irb(main):001:0> Thing.create(:name=>'A name for the thing')
   (0.1ms)  BEGIN
  SQL (0.3ms)  INSERT INTO `things` (`created_at`, `name`, `updated_at`) VALUES ('2011-11-02 16:33:48', 'A name for the thing', '2011-11-02 16:33:48')
   (40.3ms)  COMMIT
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):002:0> Thing.first
  Thing Load (0.7ms)  SELECT `things`.* FROM `things` LIMIT 1
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):003:0> 

UPDATE 2
Not very fine:

irb(main):003:0> Thing.create(:name=>'Another thing')
   (0.2ms)  BEGIN
  SQL (0.4ms)  INSERT INTO `things` (`created_at`, `name`, `updated_at`) VALUES ('2011-11-02 16:40:59', 'Another thing', '2011-11-02 16:40:59')
   (35.4ms)  COMMIT
=> #<Thing name: "Another thing", created_at: "2011-11-02 16:40:59", updated_at: "2011-11-02 16:40:59">
irb(main):004:0> Thing.first
  Thing Load (0.5ms)  SELECT `things`.* FROM `things` LIMIT 1
=> #<Thing name: "A name for the thing", created_at: "2011-11-02 16:33:48", updated_at: "2011-11-02 16:33:48">
irb(main):005:0> Thing.last
  Thing Load (11.8ms)  SELECT `things`.* FROM `things` ORDER BY `things`.`` DESC LIMIT 1
Mysql2::Error: Unknown column 'things.' in 'order clause': SELECT  `things`.* FROM `things`  ORDER BY `things`.`` DESC LIMIT 1
ActiveRecord::StatementInvalid: Mysql2::Error: Unknown column 'things.' in 'order clause': SELECT  `things`.* FROM `things`  ORDER BY `things`.`` DESC LIMIT 1

Rails Joins and include columns from joins table

I don't think that you can load users and profiles with join in Rails. I think that in earlier versions of Rails ( < 2.1) loading of associated models was done with joins, but it was not efficient. Here you have some explanation and links to other materials.

So even if you explicite say that you want to join it, Rails won't map it to associated models. So if you say Profile.whatever_here it will always be mapped to Profile object.

If you still want to do what you said in question, then you can call custom sql query and process results by yourself:

p = ActiveRecord::Base.connection.execute("SELECT * FROM profiles JOIN users ON profiles.user_id = users.id LIMIT 1")

and get results row by row with:

p.fetch_row

It will already be mappet to an array.

Your errors are because you are calling first_name and user method on AciveRecord::Relation object and it stores an array of Profile objects, not a single object. So

p = Profile.joins(:user).limit(1)
p[0].first_name

shoud work.

Better way to fetch only one record is to call:

p = Profile.joins(:user).first
p.first_name
p.user

But when you call p.user it will query database. To avoid it, you can use include, but if you load only one profile object, it is useless. It will make a difference if you load many profiles at a time and want to inlcude users table.

rails polymorphic association (legacy database)

You can override the column names, sure, but a quick scan of the Rails API didn't show me anywhere to override the polymorphic 'type' column. So, you wouldn't be able to set that to 'obj_name'.

It's ugly, but I think you'll need a HABTM for each type of object in your table.

You might be able to do something like this:

{:report => 'REPORT'}.each do |sym, text|
  has_and_belongs_to_many sym,
    :join_table => "person_links",
    :foreign_key => "obj_r_ident",
    :association_foreign_key => "per_ident",
    :conditions => "OBJ_NAME='#{text}'"
end

At least that way all the common stuff stays DRY and you can easily add more relationships.

---

Related Topics