This project is archived and is in readonly mode.

#4235 ✓invalid
Jason May

eager loading of associations not working in rails 3?

Reported by Jason May | March 19th, 2010 @ 06:15 PM | in 3.0.2

Foo.joins(:bar).includes(:bar) does not appear to generate the correct SQL.

Foo.joins(:bar).includes(:bar).to_sql gives me

SELECT "foo".* FROM "foo" INNER JOIN "bar" ON "bar"."id" = "bar"."bar_id"

My understanding is that this should generate

SELECT "foo".*, "bar".* FROM "foo" INNER JOIN "bar" ON "bar"."id" = "bar"."bar_id"

I just upgraded from Rails 2, converted all my named_scopes to the new arel hotness, and all eager loading of associations seems to be broken. I've tried rails3-beta and edge.

I suspect problems in references_eager_loaded_tables? in relation.rb but my fix there didn't do the job.

Am I missing something? I'm happy to submit a patch, suggestions welcome on where to investigate further. Thanks.

Comments and changes to this ticket

  • Jason May

    Jason May March 20th, 2010 @ 04:18 PM

    • no changes were found...
  • Jason May

    Jason May March 20th, 2010 @ 04:18 PM

    • Tag changed from arel activerecord bug rails3, eager, eager_loading to activerecord 3.0, rails 3, activerecord, arel, eager, eager_loading
  • Emilio Tagua

    Emilio Tagua March 22nd, 2010 @ 01:27 PM

    Hey Jason,

    Can you provide a failing test or something more specific, because i tried this on master:

    Post.joins(:comments).includes(:comments).all

    and you get this 2 queries:

    SELECT "posts". FROM "posts" INNER JOIN "comments" ON "comments"."post_id" = "posts"."id"
    SELECT "comments".
    FROM "comments" WHERE ("comments".post_id IN (1,2,4,5,7)).

    Which shows eager loading is working properly.

  • Jason May

    Jason May March 22nd, 2010 @ 05:53 PM

    I see...

    I had thought that includes(:bar) would generate an OUTER JOIN clause in the original SQL, rather than generating a second SELECT with an IN clause, i.e.

    Post.includes(:comments).all
    

    would generate (for has_many)

    SELECT "posts".*, "comments".* FROM "posts" LEFT OUTER JOIN "comments" ON "comments"."post_id" = "posts"."id"
    

    instead of

    SELECT "posts".* FROM "posts"
    SELECT "comments". FROM "comments" WHERE ("comments".post_id IN (...))
    

    My problem is that the 2-query situation doesn't allow for iterating through the results without pre-fetching everything. Doesn't that bust Arel's closure objective?

  • José Valim

    José Valim March 26th, 2010 @ 11:05 PM

    • Assigned user set to “Emilio Tagua”
    • Milestone cleared.
  • Emilio Tagua

    Emilio Tagua March 27th, 2010 @ 02:05 AM

    • State changed from “new” to “invalid”

    Hey Jason,

    This 2 queries is the expected behavior, you would get one query with an OUTER JOIN if any restriction includes a reference in conditions, order, etc. in the other table (check out references_eager_loaded_tables? in ActiveRecord::Relation).

    Fox exmaple:

    Post.find(:first, :include => :comments, :conditions => "posts.title = 'Welcome to the weblog'")

    When this doesn't happen preload is used which executes this 2 queries, which do pre-fetch everything in a better way, that 2 queries are better than having just 1 with a join by default.

    I'm marking this ticket as invalid as it doesn't look like a bug but feel free to continue discussing if i'm not making things clear for you.

  • Jason May

    Jason May March 30th, 2010 @ 01:31 AM

    Hi Emilio-

    I think your example should have comments.title in the conditions instead of post.title.

    Is it possible to express your example using Arel-style syntax? Something like

    Post.includes(:comment).where(comment[:title].eq("Welcome..."))
    

    I'm not sure how to refer to comment here, the example code I could find refers to an Arel::Table method but this seems to be private.

    Thanks,
    -Jason

  • Emilio Tagua

    Emilio Tagua March 31st, 2010 @ 01:45 PM

    Jason,

    You could try something like this:

    Post.includes(:comments).where(Comment.arel_table[:title].eq("Welcome ..."))
    
  • Ryan Bigg

    Ryan Bigg October 9th, 2010 @ 10:10 PM

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

    Automatic cleanup of spam.

  • Ryan Bigg

    Ryan Bigg October 11th, 2010 @ 12:13 PM

    Automatic cleanup of spam.

  • Jeremy Kemper

    Jeremy Kemper October 15th, 2010 @ 11:01 PM

    • Milestone set to 3.0.2
  • Ryan Bigg

    Ryan Bigg October 21st, 2010 @ 03:36 AM

    Automatic cleanup of spam.

  • Jeff Kreeftmeijer

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>

Pages