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.

Better query for ASSOCIATION_ids

#906

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?

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

State: resolved
Milestone: none
Assigned to: Jeremy Kemper Jeremy Kemper
Importance: none

Activity

  1. Emilio Tagua
    Emilio Tagua
    • 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.

    August 30th, 2008 @ 10:25 PM

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

    August 30th, 2008 @ 11:02 PM

  3. Emilio Tagua
    Emilio Tagua

    Added the tests for has_many :through and has_and_belongs_to_many.

    August 30th, 2008 @ 11:20 PM

  4. Bertg
    Bertg

    This patch will fail if an other column is defined as the id column.

    The 'self.class.primary_key' property should be used...

    August 31st, 2008 @ 12:40 AM

  5. Jeremy Kemper
    Jeremy Kemper

    First patch was applied in http://github.com/rails/rails/co...

    Could you rebase the fix against master?

    August 31st, 2008 @ 12:58 AM

  6. Emilio Tagua
    Emilio Tagua

    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.

    August 31st, 2008 @ 01:16 AM

  7. Repository
    Repository
    • State changed from open to resolved

    (from [6183e55f714b436335dc843528be7525c342d922]) Use reflection primary_key instead of id for when selecting association ids.

    [#906 Better query for ASSOCIATION_ids state:resolved]

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

    August 31st, 2008 @ 01:40 AM