CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-3activerecordarel
Iamvery
Iamvery

Reputation: 284

Rails 3.2 (ActiveRecord) association order with eager loading and ordering

I came upon this behavior in rails that I can't seem to find in the documentation.

It appears that if you complicate an ActiveRecord query to the point that it lumps the underlying SQL into a "complicated" query (see below) it doesn't honor ordering specified on the associations.

Here's my example model:

class Article < ActiveRecord::Base
  belongs_to :user
  has_many :categories, :order => :name
  attr_accessible :title, :user_id, :user
end

If I perform a "simple" query, I can get the associated categories ordered by their name:

> Article.includes(:categories).first.categories.map &:name
  Article Load (0.1ms)  SELECT "articles".* FROM "articles" LIMIT 1
  Category Load (0.2ms)  SELECT "categories".* FROM "categories" WHERE "categories"."article_id" IN (11) ORDER BY name
 => ["category 3059", "category 3212", "category 3240", "category 3651", "category 4371", "category 5243", "category 6176", "category 6235", "category 6468", "category 654", "category 6804", "category 6892", "category 7026", "category 8929", "category 9653"]

You can see that the categories' names are in the expected order (note: I expect the 3 digit numbered categories to be in alphabetical order due to how the database orders strings).

This next example is a more complicated query using another associations attribute for ordering:

> Article.includes(:user, :categories).order('users.name').first.categories.map &:name
  Article Load (0.3ms)  SELECT DISTINCT "articles".id FROM "articles" LEFT OUTER JOIN "users" ON "users"."id" = "articles"."user_id" LEFT OUTER JOIN "categories" ON "categories"."article_id" = "articles"."id" ORDER BY users.name LIMIT 1
  SQL (0.2ms)  SELECT "articles"."id" AS t0_r0, "articles"."title" AS t0_r1, "articles"."user_id" AS t0_r2, "articles"."created_at" AS t0_r3, "articles"."updated_at" AS t0_r4, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."created_at" AS t1_r2, "users"."updated_at" AS t1_r3, "categories"."id" AS t2_r0, "categories"."name" AS t2_r1, "categories"."article_id" AS t2_r2, "categories"."created_at" AS t2_r3, "categories"."updated_at" AS t2_r4 FROM "articles" LEFT OUTER JOIN "users" ON "users"."id" = "articles"."user_id" LEFT OUTER JOIN "categories" ON "categories"."article_id" = "articles"."id" WHERE "articles"."id" IN (16) ORDER BY users.name
 => ["category 5023", "category 728", "category 3306", "category 8170", "category 5957", "category 7190", "category 4427", "category 3435", "category 1274", "category 7251", "category 7368", "category 682", "category 2918"]

As you can see, when AR lumps the query into one of its "complicated" SQL queries order is lost to the categories association.

Is this behavior expected? Is it documented somewhere that I haven't been able to find?

Thanks!

Upvotes: 0

Views: 5784

Answers (1)

hsgubert
hsgubert

Reputation: 2264

The thing here is: you're doing a join and ordering it by 'users.name'.

When you do a join you put 2 tables together, and any ordering will affect the whole joined table.

Since youre doing 2 queries anyway, I would write it like that:

Article.includes(:user, :categories).order('users.name').first.categories.order(:name).map &:name

Or even better, use the pluck method. It returns only one column values:

Article.includes(:user, :categories).order('users.name').first.categories.order(:name).pluck(:name)

That way you don't have to load all categories attributes to list their names. =)

PS:

If you want to do double ordering you can pass two parameters to order:

Article.includes(:user, :categories).order('users.name', 'categories.name')

I didn't write the whole query because I didn't understand what exactly are you attempting to recover. If you explain more I can give it a try. Up to now I understood that you're trying to get all categories, sorted by name, of an article that belongs to the first user, sorted by name. But for me that query does not make much sense without context.

Upvotes: 3

Related Questions