ActiveRecord group by, count and joins at Tatva-Artha

ActiveRecord group by, count and joins

Performing a join query on a one-to-many relationship to obtain the count of many side is not an uncommon query that we encounter. Some examples include a need to find how many times a particular customer placed an order, how many students are enrolled in each of the classes offered this semester or to put it in a general form: how many child objects there are for a given parent object.

While this can be performed in a single SQL statement, creating corresponding ActiveRecord find options presents a few challenges. It took me a few hours to get this and I learnt a few things along the way. Here is the journey as I took it:

Assuming we have following relationship:

class Parent
  has_many :children
end

class Child
  belongs_to :parent
end

The most trivial (= inefficient) form would be:

parents = Parent.find(:all, :conditions => some_condition)
parents.each do |p|
  puts "#{parent.name} (#parent.children.count})"
end

This is inefficient because of N+1 select problem.

Since I only need the name of parent and child count, there must be an efficient way. After all, I can do this in single SQL as:

SELECT Parent.name, count(*) as count
FROM Parent
INNER JOIN Child ON Child.parent_id = Parent.id
WHERE Parent.name like '%<search_word>%'
GROUP BY (Parent.name)

I could simply take this and put in ActiveRecord’s find_by_sql. However, there are reasons why we may want to avoid using find_by_sql as much as I can, explicit table naming for one.

If I translate this to ActiveRecord, I end up with something like:

Parent.find(:all,
 :select => "Parent.name, count(*) as child_count",
 :conditions => ["Parent.name like ?", "%#{search_word}%"],
 :joins => [:children],
 :group => "Parent.name"
)

Yes, “count(*) as child_count” can simply be added as another select column when :group is specified. Importantly, the returned instance of Parent class have a method called “child_count” on them that returns the count that we need. If the Parent class already has a property called child_count, then this may not work properly but since we have full control over what we name our count(*), it shouldn’t be a major issue.

Another notable thing here is :joins clause. This performs a “inner join” by default. What that means is we won’t be able to retrieve any parent’s that don’t have any child. If we need all parents including the ones that don’t have any child then we need to modify :joins clause as:

Parent.find(:all,
  :select => "Parent.name, count(*) as child_count",
  :conditions => ["Parent.name like ?", "%#{search_word}%"],
  :joins => "left outer join Child ON Child.parent_id = Parent.id"
  :group => "Parent.name"
)

This does bring all parents. However, the count(*) is incorrectly displayed as 1 even for parents that didn’t have any child. To fix this, we would have replace count(*) with count(Child.id).

Parent.find(:all,
  :select => "Parent.name, count(Child.id) as child_count",
  :conditions => ["Parent.name like ?", "%#{search_word}%"],
  :joins => "left outer join Child ON Child.parent_id = Parent.id"
  :group => "Parent.name"
)

This is as good as it gets.

As a final note, if I wanted to display only parents with 2 or more child, I would need some kind of ‘HAVING” clause. However, :having option is not supported in ActiveRecord. A note here suggests ploppoing ‘having’ criteria along with :group option. So, I get this:

Parent.find(:all,
  :select => "Parent.name, count(Child.id) as child_count",
  :conditions => ["Parent.name like ?", "%#{search_word}%"],
  :joins => "left outer join Child ON Child.parent_id = Parent.id"
  :group => "Parent.name having child_count >= 2"
)

All in all, activerecord hides a lot if the day-to-day complexity. However, sometimes it helps to have your SQL skills around to find your way in ActiveRecord or any database mapping tool for that matter. And, if none of them work, all mapping tools provide back-door opening like find_by_sql.