title: Order by count on has_many relation
body: <p>This is a problem that I am stumbling across frequently. There have been some similar questions about this problem, but none of them was very complete (And they might possibly be outdated since Rails 4 might have introduced new functions that help with this problem)</p>
  
  <p>Let me give a simple example of the problem and the known ways to 'solve' the problem: </p>
  
  <hr>
  
  <p>Say I have a <code>User</code> Model and a <code>Post</code> Model, and a <code>User has_many :posts</code></p>
  
  <p>Now, I want to get a top five of the users with the most posts.</p>
  
  <p>The following are options I know, but they all have their own drawbacks:</p>
  
  <p>1)</p>
  
  <pre><code>users = User.all
  @top_users = users.sort {|a,b| a.posts.count &lt;=&gt; b.posts.count}.take(5)
  </code></pre>
  
  <p>Drawbacks: A DataBase request is made for each user, making this solution very slow.</p>
  
  <p>2) Use SQL code directly with a Join 
  (See for instance <a href="https://stackoverflow.com/questions/8696005/rails-3-activerecord-order-by-count-on-association">this question and answer</a>)</p>
  
  <pre><code>select('users.*, COUNT(posts.id) AS posts_count').joins(:posts).group('users.id').order('posts_count DESC').take(5)
  </code></pre>
  
  <p>This runs all sorting logic in the DataBase. However: </p>
  
  <ul>
  <li>We use a lot of DB-specific code (In PostgreSQL for example we would need other syntax). It would be better to use ActiveRecord methods, if possible.</li>
  <li>Using an Inner Join means that users without any posts will never be returned. This is a problem when we want to return users without posts as well.</li>
  </ul>
  
  <p>3) Use SQL directly with an Outer Join (see for instance <a href="https://stackoverflow.com/questions/22703820/order-by-count-on-has-many-through">this question and answers</a>)</p>
  
  <pre><code>User.select("users.*, COUNT(posts.id) as posts_count").joins("LEFT OUTER JOIN posts ON posts.user_id = users.id").group("posts.id").order("posts_count DESC")
  </code></pre>
  
  <p>This also returns users without posts. Drawbacks: </p>
  
  <ul>
  <li>Even more DB-specific code as #2, and even harder to read.</li>
  </ul>
  
  <p>4) Use a Counter Cache Column
  (For a full explanation of this technique, see <a href="http://railscasts.com/episodes/23-counter-cache-column?view=asciicast" rel="nofollow noreferrer">this Railscasts episode</a>)</p>
  
  <p>basically, create a new column on the <code>User</code> that keeps track of the current count of <code>posts</code> for that user by changing the value in the field each time a new post is created or deleted.</p>
  
  <p>This is very fast and readable. The drawback is that we can only use this after we have defined a new field on the <code>User</code>. For many situations this is acceptable, but it will be harder to make flexible because the users table needs to be changed for this to work per association we might want to create a top-five for. Also, as this is a cached field, there are database manipulations that will not trigger an update on the field.</p>
  
  <p>Is there a nicer(readable and efficient) way to accomplish this? Preferable something that uses built-in ActiveRecord methods.</p>