#2421 Problem with has_many :through and alternate keys - Ruby on Rails

Type	To find
responsible:me	tickets assigned to you
tagged:"@high"	tickets tagged @high
milestone:next	tickets in the upcoming milestone
state:invalid	tickets with the state invalid
created:"last week"	tickets created last week
sort:number, importance, updated	tickets sorted by #, importance or updated
Combine keywords for powerful searching.
Use advanced searching »

This project is archived and is in readonly mode.

#2421 ✓resolved

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

weird_associations.tar.gz 80.6 KB

Comments and changes to this ticket

You flagged this item as spam.
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
```
- 0001-use-correct-primary_key.patch 1.2 KB
- 0002-use-correct-quote_value-method.patch 1.1 KB
Jeremy Kemper May 4th, 2010 @ 06:48 PM
- Milestone changed from “2.x” to “3.x”
[bulk edit]
You flagged this item as spam.
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 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 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.

You flagged this item as spam.
Mimosa August 23rd, 2010 @ 04:29 AM
Options for :class_name and :foreign_key are ignored
You flagged this item as spam.
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 November 1st, 2010 @ 05:05 PM
- Tag cleared.
- Importance changed from “” to “Low”
Automatic cleanup of spam.
You flagged this item as spam.
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.
- 0001-use-correct-primary_key.patch 774 Bytes
- 0002-use-correct-quote_value-method.patch 771 Bytes
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.]
- 2421_has_many_through_with_alternate_keys.diff 8.6 KB
Jon Leighton December 15th, 2010 @ 11:33 PM
- Tag set to “patch”
You flagged this item as spam.
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 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 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...
You flagged this item as spam.
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.
You flagged this item as spam.
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 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
You flagged this item as spam.
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.
You flagged this item as spam.
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 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.