Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

Problem with has_many :through and alternate keys

#2421

I have a weird legacy database schema and tried to put a rails model on top of it. The database uses multiple candidate keys in a table and defines relations using alternate keys. Luckyly rails seems to handle this by the :primary_key attribute of the has_xxxx associations.

Given the three model classes with their associations:


class Customer < ActiveRecord::Base
  has_many :invoices, :primary_key => :customer_alternate_key, :foreign_key => :customer_foreign_key
  has_many :line_items, :through => :invoices
end

class Invoice < ActiveRecord::Base
  has_many :line_items, :primary_key => :invoice_alternate_key, :foreign_key => :invoice_foreign_key
end

class LineItem < ActiveRecord::Base
end

I found the has_many associations work but the has_many :through does not. After populating a test database I did:


c = Customer.find :first
l = c.line_items

and got an empty list. The database query was


SELECT * FROM "customers" LIMIT 1
SELECT "line_items".* FROM "line_items" INNER JOIN "invoices" ON "line_items".invoice_foreign_key = "invoices".id WHERE (("invoices".customer_foreign_key = 953125641))

with 953125641 being the primary key (:id) of the first customer.

This should have been


SELECT * FROM "customers" LIMIT 1
SELECT "line_items".* FROM "line_items" INNER JOIN "invoices" ON "line_items".invoice_foreign_key = "invoices".invoice_alternate_key WHERE (("invoices".customer_foreign_key = 'C7701'))

with C7701 being the alternate key (:customer_alternate_key) of the first customer.

Please find a rails sample app attached. Just run:

rake db:migrate rake test

Reported by Uwe Hees · April 5th, 2009 @ 03:55 PM

State: resolved
Milestone: 3.x
Assigned to: Aaron Patterson Aaron Patterson
Importance: Low

Activity

  1. Reto
    Reto
    • Assigned user set to Pratik

    I have a similar problem here, but in my case its a has-many through has_many relation.

    # Shift ---< ShiftTask >--- Task

# Shift(group_id, name, ...)
class Shift < ActiveRecord::Base
  has_many :shift_tasks, :primary_key => :group_id
  has_many :tasks, :through => :shift_tasks, :primary_key => :group_id
end

# ShiftTask(:id, :shift_id, :task_id ) 
class ShiftTask < ActiveRecord::Base
  belongs_to :shift, :primary_key => :group_id
  belongs_to :task, :primary_key => :group_id
end

# Task(group_id, name, ...)
class Task < ActiveRecord::Base
  has_many :shift_tasks, :primary_key => :group_id
  has_many :shifts, :through => :shift_tasks, :primary_key => :group_id
end

    The attached patch fixes the problem in my case (based on 2.3.4). Please be aware that this my first A::R patch, I'm not sure what other things might break because of it. I've run the tests and they look good and well. I've currently lack the time and skills to write dedicated tests for this case. But if somebody can tell me that my approach is the right way to go, I'm willing to invest some time into unit tests.

    One note about the patch. I've changed the owner_quoted_id of has_many_association because I think the usage of 'quote_value' is not correct there. Quote Value is implemented in ActiveRecord::Base, not in the association Proxy Class. But I'm not sure if this is only true for :through associations. In any case, the modified method could also be moved to the sub-class HasManyThroughAssociation, it works there as well.

    Pratik: I've added you to the CC because you appear to be involved in all the parts which were changed. Especially you added an empty method called 'through_reflection_primary_key_name', which sounds like exactly what I've been looking for :).

    Cheers,

    Reto

    -- SQL w/o Patch:
SELECT `tasks`.* FROM `tasks` 
  INNER JOIN `shift_tasks` ON `tasks`.id = `shift_tasks`.task_id 
  WHERE ((`shift_tasks`.shift_id = 9)) ORDER BY position ASC
    -- correct shift_id is 1

SQL w/ Patch: 
SELECT `tasks`.* FROM `tasks` 
  INNER JOIN `shift_tasks` ON `tasks`.group_id = `shift_tasks`.task_id 
  WHERE ((`shift_tasks`.shift_id = 1)) ORDER BY position ASC

    November 4th, 2009 @ 02:21 PM

  2. Jeremy Kemper
    Jeremy Kemper
    • Milestone changed from 2.x to 3.x

    May 4th, 2010 @ 06:48 PM

  3. Nick @ Reenhanced
    Nick @ Reenhanced

    Just wanted to update this with an independent observation.
    I've run into the same issue and ended up writing the identical code provided by Reto to patch my codebase.

    Reto's code is a good way to fix this issue.

    August 12th, 2010 @ 08:02 PM

  4. Mimosa
    Mimosa

    why not use change :foreign_key ?

    has_many :friendships,  :dependent => :destroy

has_many :friends, :through => :friendships, :conditions => "state = 'accepted'"

has_many :friends_activities, :class_name => 'Activity', :foreign_key => :user_id, :through => :friendships, :source => :friend

    SELECT `activities`.* FROM `activities` INNER JOIN `friendships` ON `activities`.user_id = `friendships`.friend_id WHERE `friendships`.user_id = 1

    August 23rd, 2010 @ 04:21 AM

  5. Mimosa
    Mimosa

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

    :through

    Specifies a Join Model through which to perform the query. ** Options for :class_name and :foreign_key are ignored **, as the association uses the source reflection. You can only use a :through query through a belongs_to has_one or has_many association on the join model.

    August 23rd, 2010 @ 04:28 AM

  6. Mimosa
    Mimosa

    Options for :class_name and :foreign_key are ignored

    August 23rd, 2010 @ 04:29 AM

  7. John Woods
    John Woods
    • Tag changed from 2.3.2, alternate_key, associations, has_many_through, primary_key to 2.3.2, 3.0.0, alternate_key, associations, has_many_through, primary_key

    Rails 3.0 seems to have the same problem. Reto's patch doesn't work on 3.0, but you can make the same change by hand in /activerecord-3.0.0/lib/active_record/associations/through_association_scope.rb (line 56). It seems to work fine, now, but I wish this would get patched in rails.

    October 26th, 2010 @ 11:43 PM

  8. Jeff Kreeftmeijer
    Jeff Kreeftmeijer
    • Tag cleared.
    • Importance changed from to Low

    Automatic cleanup of spam.

    November 1st, 2010 @ 05:05 PM

  9. Gustavo Gama
    Gustavo Gama

    Here's an updated (activerecord-3.0.3) and slightly modified (reuses the :primary_key option from the 'source' reflection) version of Reto's patch.

    December 15th, 2010 @ 09:38 PM

  10. Jon Leighton
    Jon Leighton
    • Assigned user changed from Pratik to Aaron Patterson

    The attached patch fixes this (and a couple of related issues), with tests.

    Note that I have consolidated construct_owner_attributes and construct_quoted_owner_attributes as there is now no need for them to be separate since the recent arel refactoring of construct_conditions.

    [This is actually already fixed in my nested :through patch (#1152), though it's of course always good to have these independent tests to guard against regressions.]

    December 15th, 2010 @ 11:33 PM

  11. Jon Leighton
    Jon Leighton
    • Tag set to patch

    December 15th, 2010 @ 11:33 PM

  12. Alex Neth
    Alex Neth

    I'm having this issue with a non-"wierd legacy database schema". I am storing facebook friendships and the join table has a friend_facebook_uid property which references the user's facebook_uid:

    class User
    has_many :facebook_friendships has_many :facebook_friends, :through => :facebook_friendships, :class_name => 'User', :source => :friend ...

    class FacebookFriendshp
    belongs_to :friend, :class_name => 'User', :foreign_key => 'friend_facebook_uid', :primary_key => 'facebook_uid' ...

    user.facebook_friendships works properly
    user.facebook_friends is always empty because it tries to join on the user.id column, ignoring the primary_key parameter.

    These patches do not work for me - they change

    SELECT "users".* FROM "users" INNER JOIN "facebook_friendships" ON "users".id = "facebook_friendships".friend_facebook_uid WHERE (("facebook_friendships".user_id = 11))

    to

    SELECT "users".* FROM "users" INNER JOIN "facebook_friendships" ON "users".id = "facebook_friendships".friend_facebook_uid WHERE (("facebook_friendships".user_id = '542552176'))

    The first patch does not even seem to be executed. Argh.

    December 15th, 2010 @ 11:59 PM

  13. Jon Leighton
    Jon Leighton

    Alex, have you tried with the patch I just uploaded? That should fix it - the test 'test_has_many_through_with_custom_primary_key_on_belongs_to_source' is targeting the exact scenario you've supplied.

    December 16th, 2010 @ 12:18 AM

  14. Repository
    Repository
    • State changed from new to resolved

    (from [14b880fd035fcdf807051398674c9aa89bd3b4d3]) Fix various issues with the :primary_key option in :through associations [#2421 state:resolved] https://github.com/rails/rails/commit/14b880fd035fcdf807051398674c9...

    December 16th, 2010 @ 01:51 AM

  15. John Woods
    John Woods

    Has this problem recurred? I'm seeing it again with the 3.1beta I just pulled from git.

    February 3rd, 2011 @ 11:55 PM

  16. John Woods
    John Woods

    Okay, a bit more information. I have:

    class Phenotype
  has_many :observations
  has_many :genes, :through => :observations
end

class Observation
  belongs_to :gene, :primary_key => :entrez_id
end

    It used to be that when I did Phenotype.find(48291).gene_ids, it would give me the entrez_id attribute rather than the primary keys. This makes sense. I don't use id for anything at all, which is why I set entrez_id as the primary. Unfortunately, in the current beta, gene_ids returns the :id attribute on each gene.

    There are obvious workarounds, but this is obnoxious, and it can't be the correct behavior.

    February 4th, 2011 @ 12:30 AM

  17. Jon Leighton
    Jon Leighton

    Hi John,

    What you've reported sounds like a slightly different thing. I've opened a ticket at https://rails.lighthouseapp.com/projects/8994-ruby-on-rails/tickets... and will look into it.

    Jon

    February 5th, 2011 @ 08:40 AM

  18. Roland Pallai
    Roland Pallai

    Seems like this problem recurred on 2.3.11. It has been worked for a while on 2.3 AFAIK.

    I've same models as Uwe Hees, his description perfectly matches with my problem.

    March 18th, 2011 @ 11:30 PM

  19. Anthony Eden
    Anthony Eden

    It looks like this issue has come back in the 3.0.6 (and likely 3.0.5 and possibly earlier versions). I tried to cherry pick just this commit into a branch based on the v3.0.6 tag, but there are conflicts so I'm going to give up for now and just work around it.

    April 12th, 2011 @ 09:52 AM

  20. Jon Leighton
    Jon Leighton

    This fix was never backported to 3-0-stable.

    I have just done that: https://github.com/rails/rails/commit/9f5ab9a9ff3c9efe4126cf00dc69e...

    It won't be backported to 2.3 - that branch is no longer being maintained.

    April 15th, 2011 @ 02:24 AM