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.

Eager loading optimisation misses if order specified

#258

When no conditions are passed into find, 2 individual queries are run. One to fetch from the table people and the other from addresses. When you provide some conditions that reference the table, the queries are combined into a single query with left outer joins.

The issue is that when you only specify an order, the individual queries still occur and the ordering throws an error as the table is not aliased.

I presume the expected behavior should be that order is checked and any included tables are left joined and aliased. 

class Person
  belongs_to :address
end

class Address
end

def my_action
  @results =Person.find(:all, :include => [:address], :order => 'addresses.street_name DESC'
end

Reported by Zyclops · May 27th, 2008 @ 03:02 AM

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

Activity

  1. Jeremy Kemper
    Jeremy Kemper
    • State changed from new to incomplete

    On what database? This works for me. Please attach a failing test case.

    May 27th, 2008 @ 03:35 AM

  2. Yannick Lecaillez
    Yannick Lecaillez

    Got same problem (rev. 9250).

    With the code published by Zyclops, try:

    Person.find(:first).address

    This generate two queries:

    SELECT * FROM `persons` LIMIT 1

    SELECT * FROM `addresses` WHERE (`addresses`.id = 1 )

    When i expected only one with a LEFT JOIN.

    June 3rd, 2008 @ 08:20 AM

  3. Jeremy Kemper
    Jeremy Kemper

    Yannick, you don't have an :order referencing the :included table. Two queries are expected.

    June 3rd, 2008 @ 08:57 AM

  4. Stefano C.
    Stefano C.
    • Tag set to 2.1, activerecord, bug, eagerloading, named_scope, order

    I have the same problem (Rails 2.1.0 via gems) with a named_scope: 

    class Release
  belongs_to :movie
  named_scope :by_title, :order => "movies.title"
end

class Movie
  has_many :releases
end

Release.by_title.find(:all)
# PGError: ERROR:  missing FROM-clause entry for table "movies"

    If I add a dummy :conditions key (e.g. "movies.title is not null") to the named_scope, it works.

    I've created a test for this particular case and it passes in HEAD. I think the bug was resolved in #372.

    June 25th, 2008 @ 04:37 PM

  5. Jeremy Kemper
    Jeremy Kemper
    • Milestone set to 2.1.1
    • Assigned user set to Jeremy Kemper

    Stefano, you don't reference the association in your example.

    June 25th, 2008 @ 06:43 PM

  6. Stefano C.
    Stefano C.

    Woops, I was trying the various combinations and I pasted the wrong one :-)

    Here is the right one, straight from my project: 

    class Release
  belongs_to :movie
  named_scope :current, :conditions => ["status in (?)", [0, 1, 2]]
  named_scope :by_title, :order => "movies.title"
end

class Movie
  has_many :releases
end

Release.current.by_title.find(:all, :include => :movie)
# SELECT * FROM "releases" WHERE (status in (0,1,2)) ORDER BY movies.title
# PGError: ERROR:  missing FROM-clause entry for table "movies"

    June 25th, 2008 @ 07:31 PM

  7. Ryan Bigg
    Ryan Bigg

    In Rails 2.3.5 this seems to eager load for me:

    >> Release.current.by_title.find(:all, :include => :movie)

  Release Load Including Associations (0.3ms)   SELECT "releases"."id" AS t0_r0, "releases"."movie_id" AS t0_r1, "releases"."created_at" AS t0_r2, "releases"."updated_at" AS t0_r3, "releases"."status" AS t0_r4, "movies"."id" AS t1_r0, "movies"."title" AS t1_r1, "movies"."created_at" AS t1_r2, "movies"."updated_at" AS t1_r3 FROM "releases" LEFT OUTER JOIN "movies" ON "movies".id = "releases".movie_id WHERE (status in (0,1,2)) ORDER BY movies.title

    April 10th, 2010 @ 08:44 AM

  8. Ryan Bigg
    Ryan Bigg
    • State changed from incomplete to resolved

    Marking as resolved, appears to be fixed in 2.3.5.

    April 12th, 2010 @ 08:52 AM