This project is archived and is in readonly mode.

#2421 ✓resolved
Uwe Hees

Problem with has_many :through and alternate keys

Reported by Uwe Hees | April 5th, 2009 @ 03:55 PM | in 3.x

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

Comments and changes to this ticket

  • Reto

    Reto November 4th, 2009 @ 02:21 PM

    • 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
    
  • Jeremy Kemper

    Jeremy Kemper May 4th, 2010 @ 06:48 PM

    • Milestone changed from 2.x to 3.x
  • Nick @ Reenhanced

    Nick @ Reenhanced August 12th, 2010 @ 08:02 PM

    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.

  • Mimosa

    Mimosa August 23rd, 2010 @ 04:21 AM

    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
    
  • Mimosa

    Mimosa August 23rd, 2010 @ 04:28 AM

    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.
    
  • Mimosa

    Mimosa August 23rd, 2010 @ 04:29 AM

    Options for :class_name and :foreign_key are ignored

  • John Woods

    John Woods October 26th, 2010 @ 11:43 PM

    • 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.

  • Jeff Kreeftmeijer

    Jeff Kreeftmeijer November 1st, 2010 @ 05:05 PM

    • Tag cleared.
    • Importance changed from “” to “Low”

    Automatic cleanup of spam.

  • Gustavo Gama

    Gustavo Gama December 15th, 2010 @ 09:38 PM

    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.

  • Jon Leighton

    Jon Leighton December 15th, 2010 @ 11:33 PM

    • 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.]

  • Jon Leighton
  • Alex Neth

    Alex Neth December 15th, 2010 @ 11:59 PM

    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.

  • Jon Leighton

    Jon Leighton December 16th, 2010 @ 12:18 AM

    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.

  • Repository

    Repository December 16th, 2010 @ 01:51 AM

    • 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...

  • John Woods

    John Woods February 3rd, 2011 @ 11:55 PM

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

  • John Woods

    John Woods February 4th, 2011 @ 12:30 AM

    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.

  • Jon Leighton

    Jon Leighton February 5th, 2011 @ 08:40 AM

    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

  • Roland Pallai

    Roland Pallai March 18th, 2011 @ 11:30 PM

    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.

  • Anthony Eden

    Anthony Eden April 12th, 2011 @ 09:52 AM

    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.

  • Jon Leighton

    Jon Leighton April 15th, 2011 @ 02:24 AM

    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.

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Tags

Referenced by

Pages