CODEWITHSUNDEEP
ruby-on-railsactiverecordruby-on-rails-5
Semen Shekhovtsov
Semen Shekhovtsov

Reputation: 882

Active record foreign key primary key issue

let's say I have two tables, they are linked by custom columns:

table1s
  id varchar
  user_id varchar

table2s
  id varchar
  linked_user_id varchar

now, I try to make a relationship in the Ruby code

class Table1 < ActiveRecord::Base
    self.primary_key = "id"

    has_many :table2s, primary_key: :user_id, inverse_of: :table1
end

class Table2 < ActiveRecord::Base
    self.primary_key = "id"

    belongs_to :table1, foreign_key: :linked_user_id, inverse_of: :table2s
end

now, when I try to make a SQL query, it uses for some reason id column instead of the specified user_id

Table2.select('table2s.*')
          .joins(:table1)
          .where('table1s.id = ?', 323235)

the generated SQL looks like below:

SELECT table2s.*
FROM table2s
INNER JOIN table1s ON table1s.id = table2s.linked_user_id
WHERE table1s.id = 323235

I was expecting it to be user_id not the id:

SELECT table2s.*
FROM table2s
INNER JOIN table1s ON table1s.user_id = table2s.linked_user_id
WHERE table1s.id = 323235

any thoughts?

Upvotes: 1

Views: 782

Answers (1)

engineersmnky
engineersmnky

Reputation: 29318

If you have a custom relationship where the keys differ from the primary_key for the class (table) then you need to add both foreign_key and primary_key to the relationship in order for the join to work correctly. e.g.

class Table1 < ActiveRecord::Base
    self.primary_key = "id"

    has_many :table2s, primary_key: :user_id, foreign_key: :linked_user_id, inverse_of: :table1
end

class Table2 < ActiveRecord::Base
    self.primary_key = "id"

    belongs_to :table1, primary_key: :user_id, foreign_key: :linked_user_id, inverse_of: :table2s
end

belongs_to From the Docs

:primary_key Specify the method that returns the primary key of associated object used for the association. By default this is id.

has_many From the Docs

:foreign_key Specify the foreign key used for the association. By default this is guessed to be the name of the association with an “_id” suffix. So a class that defines a belongs_to :person association will use “person_id” as the default :foreign_key. Similarly, belongs_to :favorite_person, class_name: "Person" will use a foreign key of “favorite_person_id”.

Upvotes: 3

Related Questions