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:
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
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
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
Note that the trick we just introduced can be used together with other SQL aggregate functions, like
AVG(), not only the