Rails ActiveRecord is a quite useful piece of software, though it lacks some features and way of achieving the desired result is not always obvious.

Let’s assume that we have two tables in our database: categories and posts. We would like to get most essential categories; i.e. those having at least 10 posts assigned to them. We can achieve this using the SQL JOIN, GROUP BY and HAVING clauses. However, there is no :having parameter on the find() method’s parameter list.

Writing the whole SQL query is one of the workarounds, but there is a better one. We can simply add the HAVING clause to the :group parameter, like in the following find() invocation:

def self.most_essential_categories
  find(:all, :select => 'categories.name, COUNT(posts.id) AS posts_count',
             :joins  => 'INNER JOIN posts ON posts.category_id = categories.id',
             :group  => 'categories.id, categories.name HAVING COUNT(posts.id) >= 10')
end

That’s it, the most_essential_categories() method returns names and posts count of categories having at least 10 posts assigned to them. INNER JOIN ensures that only categories having at least one post are being taken into account. By the way, the INNER keyword can be omitted due to the HAVING COUNT(posts.id) >= 10 condition and because INNER is the default JOIN type.

Note that the trick we just introduced can be used together with other SQL aggregate functions, like MIN(), MAX() or AVG(), not only the COUNT() function.