SQL HAVING Clause in Rails ActiveRecord

November 12, 2008 / category: Ruby on Rails / 0 comments

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.

My eBook: “Memoirs of a Software Team Leader”
Read more »


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 ommited 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.

Comments

There are no comments yet / Submit your comment

You can use Markdown in your comments if you wish. Examples:

*emphasis*
emphasis
**strong**
strong
`inline code`
inline code
[My blog](http://lukaszwrobel.pl)
My blog
# use 4 spaces to indent
# a block of code
    def my_method(x)
      x = x + 1
    end
def my_method(x)
  x = x + 1
end

* First.
* Second.
  • First.
  • Second.

> This is a citation.
> Even more citation.

I don't agree with you.

This is a citation. Even more citation.

I don't agree with you.


Submit your comment

(required)

(optional)

(required, Markdown supported)


Preview:

My eBook: “Memoirs of a Software Team Leader”

Read more »