Eager loading associations with ordering

This was a sneaky bug that I managed to track down in my code... I hope it helps someone. I first thought this might be a shortcoming or a bug in Rails but I'm not really sure where the fault lies: I guess it's just an unexpected or suprising behaviour, or else my misuse of ActiveRecord's magic.

# Problem simplified down to the ubiquitous Project/Task paradigm.
# Let's use acts_as_list for convenience.

class Project < ActiveRecord::Base
  has_many :tasks, :order => :position

  def next_action
    tasks.first
  end
end

class Task < ActiveRecord::Base
  belongs_to :project
  acts_as_list :scope => :project_id
end

# Let's create a project with some tasks and move
# the last one to the top, as might happen in normal use:

p = Project.create :name => "Halloween costume"
task_names = ["paint wings", "recharge lightsaber", "get bear slippers"]
task_names.each do |t|
  p.tasks << Task.new :name => t
end
p.tasks.last.move_to_top

# Now in our controller, we want to select all projects ordered by name.
# We'll also want to display the next action... so let's
# eager load the tasks association:

@projects = Project.find :all, :order => :name, :include => :tasks

# in the view:

<% @projects.each do |project| %>
<%= project.next_action %>
<% end %>

Surprise! the next action is not the one we moved to the top! What I'm understanding is that the find action uses only :order => :name, and replaces the position ordering which is specified in the model.

Thoughts:

Could Rails handle this more intelligently for us? I had expected that it would pile on the :order statements like "ORDER BY project.name, task.position" but it doesn't seem to do that at all.

Or else, should rails put up a warning when something like this occurs (when an existing ordering is overwritten)?

To work around this, Instead of the method above, I added a proxy on the Project, replacing the next_action method above:

  has_one :next_action, :class_name => 'Task', :conditions => { :position => 1 }

This seems to work, and I can now include just the next_action association where needed. Problem solved. But, this works because we're no longer using a has_many association at all. I'm still wondering if it's possible to maintain multiple ordering with has_many... Any thoughts on this?

--Andrew Vit

Could Rails handle this more intelligently for us? I had expected that
it would pile on the :order statements like "ORDER BY project.name,
task.position" but it doesn't seem to do that at all.

You're correct, it's ignored. The eager loading code is basically hellish enough as it is. Assuming it did work, you might also have problems if for whatever reason the associations you were eager loading required conflicting orderings (and it might not be completely obvious to work out this was the case). I've actually got a patch (#9640) in trac currently that reimplements :include in such a way that a lot of things (including order) work a lot nicer, but it's still a work in progress

Or else, should rails put up a warning when something like this occurs
(when an existing ordering is overwritten)?

Quite possibly.

Fred