title: Sort a query based on an association 2 or more levels deep
body: <p>I have the following models</p>
  
  <pre><code>class Post &lt; ActiveRecord::Base
    belongs_to :user, :counter_cache =&gt; true
    belongs_to :subtopic      
  end
  
  
  class Subtopic &lt; ActiveRecord::Base
    belongs_to :category
    has_many :posts, :dependent =&gt; :destroy
  end
  
  class Category &lt; ActiveRecord::Base
    has_many :posts, :through =&gt; :subtopics
    has_many :subtopics, :dependent =&gt; :destroy
  end
  </code></pre>
  
  <p>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:</p>
  
  <ul>
  <li>post attributes
  <ul>
  <li>subtopic name<br>
  <ul>
  <li>category name</li>
  </ul></li>
  </ul></li>
  </ul>
  
  <p>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 </p>
  
  <pre><code>@posts = Post.includes(:user, :subtopic =&gt; :category).paginate(:page =&gt;1, :order =&gt; 'subtopics.name ASC')
  </code></pre>
  
  <p>This returns the results I want.  When I try going one level deeper with</p>
  
  <pre><code>@posts = Post.includes(:user, :subtopic =&gt; :category).paginate(:page =&gt;1, :order =&gt; 'subtopic.categories.name ASC')
  </code></pre>
  
  <p>I get <code>PGError: ERROR:  schema "subtopic" does not exist</code>.  Pluralizing subtopic gives the same error.  I feel like I'm missing something obvious.  Can someone point it out?</p>
  
  <p><strong>Side note</strong>: 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.</p>
  
  <p><hr>
  <strong>UPDATE 8/3</strong>: ActiveRecord or PostgreSQL is causing another error in the controller action.  The following statement is valid in the database console:</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>The SQL statement above is <em>approximately</em> what's generated from the statement below.  The rails code below is valid in <code>rails console</code> but <strong>not</strong> in my controller action for <code>Post#index</code></p>
  
  <pre><code> @posts = Post.includes(:user, :subtopic =&gt; :category).paginate(:page =&gt;1, :order =&gt; 'categories.category_name ASC', :conditions =&gt; ["posts.flags_count &lt; ?", Flag.flag_threshold] )
  </code></pre>
  
  <p>The error: <a href="https://gist.github.com/1124135" rel="noreferrer">https://gist.github.com/1124135</a></p>
  
  <p>This error occurs for <code>:order =&gt; 'categories. ...'</code> and <code>:order =&gt; 'subtopics. ...'</code> but <strong>not</strong> for <code>:order =&gt; (column in posts)</code>.  It looks like a problem with the JOIN statement that ActiveRecord generates, but the actual SQL statement looks like it should work to me.</p>
  
  <hr>
  
  <p><strong>Update 8/4</strong>: When I enable SQL logging in my environment (using <a href="https://gist.github.com/1126066" rel="noreferrer">a shell script</a>), I can see that the SQL queries that ActiveRecord generates in <code>rails console</code> and (a controller in) <code>rails server</code> are different.</p>
  
  <p>Specifically, on the server, AR is prepending certain columns with 'post.', such as <code>posts.users.display_name</code> instead of <code>users.display_name</code>.  This only happens <strong>on the server</strong>, and only when I include in the query <code>:order =&gt; (non-posts column) ASC/DESC</code></p>