Sorting a query based on an association of 2 or more levels in depth

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 

On the posts / index page, I want to add click sorting to the column headings. This is fine, except that I display some information at two levels:

• post attributes
  • subtopic title
    
    • name of category

I want the "Category" column of the table to be clickable so that I can display all messages sorted by their category. Unfortunately, when I create the query part in order, I cannot get anything to work for the category name. I can go one level with

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

This returns the results I want. When I try to go 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 . The pluralizing subtopic gives the same error. I feel like I'm missing something obvious. Can anyone point this out?

Side Note : Bad Odor Warning. I know. If you have suggestions on how to make this code cleaner, that would be very welcome. I know the Law of Demeter, but I don’t see how to get the final result that I want without breaking it.

---

UPDATE 8/3 : ActiveRecord or PostgreSQL causes another error in the controller action. The following statement applies to 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 above SQL statement roughly matches what is created from the statement below. The rails code below is valid in the 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] ) 

Error: https://gist.github.com/1124135

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

---

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

In particular, on the server, AR adds some columns with "post", for example posts.users.display_name instead of users.display_name . This only happens on the server , and only when I include in the request :order => (non-posts column) ASC/DESC

Answer 1

The string in your order proposal is inserted into the resulting SQL pretty much as it is. Thus, the resulting request turns out to be something like:

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

And the database doesn't know what that means. You want to order by 'categories.name ASC'.

UPDATE : Okay, now will_paginate chokes on the mix. 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 make you go. I would further clean my controller by moving all of these crawlers to a model, perhaps like an area named "by_category_order" or something like that. Then my controller code will be nice and beautiful:

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