Sort a query based on an association 2 or more levels deep

Question

I have the following models

class Post < ActiveRecord::Base
  belongs_to :user, :counter_cache => true
  belongs_to :subtopic      
end


class Subtopic < ActiveRecord::Base
  belongs_to :category
  has_many :posts, :dependent => :destroy
end

class Category < ActiveRecord::Base
  has_many :posts, :through => :subtopics
  has_many :subtopics, :dependent => :destroy
end

In the posts/index page, I want to add click-able sorting to the column titles. This is fine, except that I display some information two levels deep:

• post attributes
  • subtopic name
    
    • category name

I want the "Category" column of the table to be click-able, such that I can display all posts sorted by their category. Unfortunately, when I construct the "order by" part of the query, I can't seem to get anything to work for category name. I can go one level deep with

@posts = Post.includes(:user, :subtopic => :category).paginate(:page =>1, :order => 'subtopics.name ASC')

This returns the results I want. When I try going one level deeper with

@posts = Post.includes(:user, :subtopic => :category).paginate(:page =>1, :order => 'subtopic.categories.name ASC')

I get PGError: ERROR: schema "subtopic" does not exist. Pluralizing subtopic gives the same error. I feel like I'm missing something obvious. Can someone point it out?

Side note: Bad smell alert. I know. If you have suggestions on how to make this code cleaner as well, that would be very welcome. I'm aware of the Law of Demeter, but I don't see how to get the end result I want without breaking it.

---

UPDATE 8/3: ActiveRecord or PostgreSQL is causing another error in the controller action. The following statement is valid in the database console:

SELECT * from "posts" LEFT OUTER JOIN "users" ON "users"."id" = "posts"."user_id" LEFT OUTER JOIN "subtopics" ON "subtopics"."id" = "posts"."subtopic_id" LEFT OUTER JOIN "categories" ON "categories"."id" = "subtopics"."category_id" ORDER BY categories.name ASC LIMIT 30;

The SQL statement above is approximately what's generated from the statement below. The rails code below is valid in rails console but not in my controller action for Post#index

 @posts = Post.includes(:user, :subtopic => :category).paginate(:page =>1, :order => 'categories.category_name ASC', :conditions => ["posts.flags_count < ?", Flag.flag_threshold] )

The error: https://gist.github.com/1124135

This error occurs for :order => 'categories. ...' and :order => 'subtopics. ...' but not for :order => (column in posts). It looks like a problem with the JOIN statement that ActiveRecord generates, but the actual SQL statement looks like it should work to me.

---

Update 8/4: When I enable SQL logging in my environment (using a shell script), I can see that the SQL queries that ActiveRecord generates in rails console and (a controller in) rails server are different.

Specifically, on the server, AR is prepending certain columns with 'post.', such as posts.users.display_name instead of users.display_name. This only happens on the server, and only when I include in the query :order => (non-posts column) ASC/DESC

Answer 1

The string in your order clause is inserted into the resulting SQL pretty much as-is. So the resulting query turns out to be something like:

Select...order by subtopic.categories.name ASC

And the database has no idea what that means. You want to order by 'categories.name ASC'.

UPDATE: Ok, now will_paginate is choking on the join. I would do something like this:

@posts = Post.includes(:user, :subtopic => :category).where(["posts.flags_count < ?", Flag.flag_threshold]).order('categories.category_name ASC').paginate(:page =>1)

That should get you going. I would further clean up my controller by moving all of that finder stuff into the model, probably as a scope named 'by_category_order' or something. Then my controller code would be nice and pretty like:

@posts = Post.by_category_order.paginate(:page => params[:page])