grouping - Rails has_many association count child rows

Question

What is the "rails way" to efficiently grab all rows of a parent table along with a count of the number of children each row has?

I don't want to use counter_cache as I want to run these counts based on some time conditions.

The cliche blog example: Table of articles. Each article has 0 or more comments.

I want to be able to pull how many comments each article has in the past hour, day, week.

However, ideally I don't want to iterate over the list and make separate sql calls for each article nor do I want to use :include to prefetch all of the data and process it on the app server.

I want to run one SQL statement and get one result set with all the info.

I know I can hard code out the full SQL, and maybe could use a .find and just set the :joins, :group, and :conditions parameters... BUT I am wondering if there is a "better" way... aka "the rails way"

Thanks in advance

Answer 1

This activerecord call should do what you want:

Article.find(:all, :select => 'articles.*, count(posts.id) as post_count',
             :joins => 'left outer join posts on posts.article_id = articles.id',
             :group => 'articles.id'
            )

This will return a list of article objects, each of which has the method post_count on it that contains the number of posts on the article as a string.

The method executes sql similar to the following:

SELECT articles.*, count(posts.id) AS post_count
FROM `articles`
LEFT OUTER JOIN posts ON posts.article_id = articles.id
GROUP BY articles.id

If you're curious, this is a sample of the MySQL results you might see from running such a query:

+----+----------------+------------+
| id | text           | post_count |
+----+----------------+------------+
|  1 | TEXT TEXT TEXT |          1 |
|  2 | TEXT TEXT TEXT |          3 |
|  3 | TEXT TEXT TEXT |          0 |
+----+----------------+------------+