CODEWITHSUNDEEP
ruby-on-railsrubyactiverecord
Jose
Jose

Reputation: 601

Rails has_many :through with :primary_key

I am trying to create a many-to-many relationship with rails has_many through: but instead of using the models primary key (id) I need to create the relationship using a different column. Here are my models (Btw, I am using Rails 4):

class Food < ActiveRecord::Base
  validates :NDB_No, uniqueness: true
  validates :NDB_No, :FdGrp_Cd, :Long_Desc, :Shrt_Desc, presence: true

  has_many :langual_factor_associations, primary_key: 'NDB_No', foreign_key: 'NDB_No'
  has_many :langual_factor_descriptions, through: :langual_factor_associations, primary_key: 'NDB_No', foreign_key: 'NDB_No'
end

class LangualFactorAssociation < ActiveRecord::Base
  validates :NDB_No, :Factor_Code, presence: true

  belongs_to :food, foreign_key: 'NDB_No'
  belongs_to :langual_factor_description, foreign_key: 'Factor_Code'
end

class LangualFactorDescription < ActiveRecord::Base
  validates :Factor_Code, uniqueness: true
  validates :Factor_Code, :Description, presence: true

  has_many :langual_factor_associations, primary_key: 'Factor_Code', foreign_key: 'Factor_Code'
  has_many :foods, through: :langual_factor_associations, primary_key: 'Factor_Code', foreign_key: 'Factor_Code'

end

The has_many association with LangualFactorAssociation works correctly for both Food and LangualFactorDescription. But the has_many through: association between Food and LangualFactorDescription does not work. Here is the error I get when I try to access Food.LangualFactorDescriptions:

Food::should create the proper relations to the LangualFactorDescription
            model#test_0002_must create the proper associations:
ActiveRecord::StatementInvalid: PG::Error: ERROR:  operator does not exist: integer = character varying
LINE 1: ...sociations" ON "langual_factor_descriptions"."id" = "langual...
                                                             ^
HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
: SELECT  "langual_factor_descriptions".* FROM "langual_factor_descriptions" INNER JOIN "langual_factor_associations" ON "langual_factor_descriptions"."id" = "langual_factor_associations"."Factor_Code" WHERE "langual_factor_associations"."NDB_No" = $1  ORDER BY "langual_factor_descriptions"."id" ASC LIMIT 1
    test/models/food_test.rb:172:in `block (3 levels) in <top (required)>'

I think the problem is this part of the query ON "langual_factor_descriptions"."id" = "langual_factor_associations"."Factor_Code". I thought that setting primary_key and/or foreign_key options would fix this but it didn't. In fact if I remove those from the model and just leave it like

has_many :langual_factor_descriptions, through: :langual_factor_associations

rails produces the exact same query, so it seems to me that setting those options does nothing. Am I missing something here? Any idea on how I can tell rails to do not look for the langual_factor_descriptions.id but instead langual_factor_descriptions.Factor_Code?

Here are some of the most relevant resources that I have read about this topic: http://guides.rubyonrails.org/association_basics.html

http://api.rubyonrails.org/classes/ActiveRecord/Associations/ClassMethods.html

has_many :through with :primary_key on join table not working (this is pretty much the problem I have but I am not sure that's the proper solution)

Rails has_many association with multiple keys

https://www.ruby-forum.com/topic/139765

Has Many Through Alternative Primary and Foreign Keys

Upvotes: 4

Views: 5735

Answers (1)

Jose
Jose

Reputation: 601

I think I fixed the problem. Here is the code:

class Food < ActiveRecord::Base
  validates :NDB_No, uniqueness: true
  validates :NDB_No, :FdGrp_Cd, :Long_Desc, :Shrt_Desc, presence: true

  has_many :langual_factor_associations, primary_key: 'NDB_No', foreign_key: 'NDB_No'
  has_many :langual_factors, through: :langual_factor_associations    
end

class LangualFactorAssociation < ActiveRecord::Base
  validates :NDB_No, :Factor_Code, presence: true

  belongs_to :food, primary_key: 'NDB_No', foreign_key: 'NDB_No'
  belongs_to :langual_factor, primary_key: 'Factor_Code', foreign_key: 'Factor_Code'
end

class LangualFactor < ActiveRecord::Base
  validates :Factor_Code, uniqueness: true
  validates :Factor_Code, :Description, presence: true

  has_many :langual_factor_associations, primary_key: 'Factor_Code', foreign_key: 'Factor_Code'
  has_many :foods, through: :langual_factor_associations    
end

Notice that I didn't use foreign_key nor primary_key options in the has_many through: associations, and that there is no need for self.primary_key.

Also, here are some other useful links that helped me:

http://railsforum.com/viewtopic.php?id=36186

http://guides.rubyonrails.org/v2.3.11/active_record_querying.html

Even though the solution to the problem came by coincidence, I believe those links provide relevant information.

Upvotes: 4

Related Questions