Rails 4: Odd Activerecord sort order

Question

Like in this question I am looking to eager load another object and then sort the list based on a value from the eager loaded object.

Given parent Foo and child Bar:

class Foo < ActiveRecord::Base
  has_many :bar, foreign_key: :foo_name, primary_key: :name   
  has_one  :most_recent_bar, -> { order created at desc }, foreign_key: :foo_name, primary_key: :name   
end

class Bar < ActiveRecord::Base
  belongs_to :foo, foreign_key: :foo_name, primary_key: :name   
end

I am looking to show a list of Foos, and then sort them by Bar.value and Foo.name. I am only looking for the most recently created Bar, so it's sorted in reverse order and limited to 1. So:

@foos = Foo.includes(:most_recent_bar).order('bar.value asc, foo.name asc')   

The problem: The order statement above is changing most_recent_bar. It is ordering all Foo.bars and then picking the most recent of the sorted list. It is only then ordering the list by Foo.name.

The Question: How can I appropriately order these eager loaded values such that most_recent_bar is legitimately the most recently created, and then order the list of Foos based on most_recent_bar.value?

Edit for SQL from my live code:

    Foo.includes(:most_recent_bar).order('bars.value asc, name asc')
  SQL (7.9ms)  SELECT "foos"."id" AS t0_r0, "foos"."name" AS t0_r1, "foos"."created_at" AS t0_r2, "foos"."updated_at" AS t0_r3, "bars"."id" AS t1_r0, "bars"."content" AS t1_r1, "bars"."created_at" AS t1_r2, "bars"."updated_at" AS t1_r3, "bars"."foo_name" AS t1_r4, "bars"."version" AS t1_r5, "bars"."bbj_status" AS t1_r6, "bars"."host" AS t1_r7, "bars"."value" AS t1_r8 FROM "foos" LEFT OUTER JOIN "bars" ON "bars"."foo_name" = "foos"."name" ORDER BY bars.value asc, name asc

Edit for broken SQL after fix Seems to be looking for two created ats, and I'm not sure where the ambiguous one is coming from!

My error SQL Foo.includes(:most_recent_foo).merge(bar.most_recent).order('bars.repo asc')
  SQL (0.6ms)  SELECT "foos"."id" AS t0_r0, "foos"."name" AS t0_r1, "foos"."created_at" AS t0_r2, "foos"."updated_at" AS t0_r3, "bars"."id" AS t1_r0, "bars"."content" AS t1_r1, "bars"."created_at" AS t1_r2, "bars"."updated_at" AS t1_r3, "bars"."foo_name" AS t1_r4, "bars"."version" AS t1_r5, "bars"."bbj_status" AS t1_r6, "bars"."host" AS t1_r7, "bars"."repo" AS t1_r8 FROM "foos" LEFT OUTER JOIN "bars" ON "bars"."foo_name" = "foos"."name" ORDER BY created_at DESC, bars.created_at desc, bars.repo asc
PG::Error: ERROR:  column reference "created_at" is ambiguous
LINE 1: …bars”.”foo_name" = "foos"."name"  ORDER BY created_at...

Answer 1

Now that I finally understand the question (I hope), here's the bad news: SQL doesn't have any simple commands for sorting in the manner you require - ORDER BY alone isn't going to suffice. Because of this, ActiveRecord doesn't either provide a simple solution (ActiveRecord is simply an ORM with a good/very good SQL statement generator). What you require is quite complex for any ORM to do efficiently.

The good news is that you can do this with a complete understanding how you can use SQL to accomplish this, and a bit of work to inform AR what you are trying to do. First, the SQL.

There are various approaches to sort by a MAX of a collection of associated rows, and the most efficient will vary by DBMS and the actual data. Because you are using PostgreSQL, you might want to use a window function to accomplish this. The problem is that using a window function to work cleanly with eager loading is going to be extremely difficult. So what other options are there using vanilla SQL? One simple approach involves using NOT EXISTS:

SELECT /* some-eager-loaded-fields */
FROM foos
LEFT JOIN bars ON foos.id = bars.foo_id
  AND NOT EXISTS (
    SELECT 1 FROM bars b2
    WHERE b2.foo_id = bars.foo_id AND b2.created_at > bars.created_at
  )
ORDER BY bars.value, foos.name

The NOT EXISTS will exclude any bars where a greater created_at exists for that foo_id. This leaves you with the only bar that has no greater created_at - the MAX. Now that we have the query, how would we generate that in AR?

The subquery would look like this (using Arel to help format the alias correctly):

b2 = Bar.arel_table.alias('b2')
Bar.select('1').from(b2).where('b2.foo_id = bars.foo_id AND b2.created_at > bars.created_at')

This can now be used to determine most recent:

def Bar < AR::Base
  def self.most_recent
    b2 = arel_table.alias('b2')
    where Bar.select('1').from(b2).
      where('b2.foo_id = bars.foo_id AND b2.created_at > bars.created_at').
      exists.not
  end
end

And your most_recent_bar association:

def Foo < AR::Base
  has_many :bars
  has_one :most_recent_bar, ->{ merge(Bar.most_recent) }, class_name: 'Bar'
end

Now your query should just work:

Foo.includes(:most_recent_bar).order('bars.value asc, foos.name asc')