#906 Better query for ASSOCIATION_ids - 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.

#906 ✓resolved

Better query for ASSOCIATION_ids

Reported by Bertg | August 26th, 2008 @ 10:58 AM

For has_many relations Imagine we have 2 models: Cart and Item. Cart has many Items.

If we execute the following statement


Cart.first.item_ids

we get the following query


SELECT * FROM `items` WHERE (`items`.cart_id = 1)

This could by retrieving only an array, and not the objects.


SELECT `items`.id FROM `items` WHERE (`items`.cart_id = 1)

For has_many :through relations Imagine 3 models: User, Membership, Account

The User has many accounts trough membership and vice versa. We define the fitted relations and Execute the following query


User.first.account_ids

This will create the following query


SELECT `accounts`.* FROM `accounts` INNER JOIN memberships ON accounts.id = memberships.account_id WHERE ((`memberships`.user_id = 1))

While the following query would be a lot more efficient (only retrieving an array, not the objects):


SELECT `memberships`.account_id FROM memberships WHERE ((`memberships`.user_id = 1))

What do you think?

Comments and changes to this ticket

Emilio Tagua August 30th, 2008 @ 10:25 PM
- Tag changed from “activerecord, eager_loading, enhancement, has_many, joins” to “activerecord, eager_loading, enhancement, has_many, joins, patch”
I attach the patch that does this, selecting only ids when the association hasn't been loaded, but in case you have already loaded the records, just map the ids has done before and prevent Rails of doing an extra query.

I also removed trailing whitespaces in the test file.
Jeremy Kemper August 30th, 2008 @ 11:02 PM
- State changed from “new” to “open”
- Assigned user set to “Jeremy Kemper”
- Milestone cleared.
The test covers has_many only, but the change affects habtm and has_many :through also.
Emilio Tagua August 30th, 2008 @ 11:20 PM
Added the tests for has_many :through and has_and_belongs_to_many.
- improve_association_ids_query_with_more_tests.diff 9 KB
You flagged this item as spam.
Bertg August 31st, 2008 @ 12:40 AM
This patch will fail if an other column is defined as the id column.

The 'self.class.primary_key' property should be used...
- improve_association_ids_query_with_more_tests.diff.txt 9 KB
Jeremy Kemper August 31st, 2008 @ 12:58 AM
First patch was applied in http://github.com/rails/rails/co...

Could you rebase the fix against master?
Emilio Tagua August 31st, 2008 @ 01:16 AM
Bertg, you are right i missed that, i attach the patch that applies on top of the first one, since it is already applied, and fix the reported issue.
- fix_association_ids.diff 1 KB
Repository August 31st, 2008 @ 01:40 AM
- State changed from “open” to “resolved”
(from [6183e55f714b436335dc843528be7525c342d922]) Use reflection primary_key instead of id for when selecting association ids.

[#906 state:resolved]

Signed-off-by: Jeremy Kemper jeremy@bitsweat.net http://github.com/rails/rails/co...