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"

Stanwood

Here is another answer. – Lunisolar

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 |
+----+----------------+------------+

Buffybuford

A quick note for those users using PostgreSQL instead, you will need to supply all the retrieved columns for the select and group. – Crusade

In Rails 3, this would be Article.select('foo').joins('bar').group('baz') etc. – Stimulative

@JaredBeck, your line causes Rails to do an INNER JOIN on the tables, which would not return any Article rows with 0 associated Post rows. – Scots

Perhaps I should have said select('foo').joins('left join bar'). I was just trying to demonstrate the new arel goodness. – Stimulative

Answer 2

Rails 3 Version

For Rails 3, you'd be looking at something like this:

Article.select("articles.*, count(comments.id) AS comments_count")
  .joins("LEFT OUTER JOIN comments ON comments.article_id = articles.id")
  .group("articles.id")

Thanks to Gdeglin for the Rails 2 version.

Rails 5 Version

Since Rails 5 there is left_outer_joins so you can simplify to:

Article.select("articles.*, count(comments.id) AS comments_count")
  .left_outer_joins(:comments)
  .group("articles.id")

And because you were asking about the Rails Way: There isn't a way to simplify/railsify this more with ActiveRecord.

Twedy

Answer 3

From a SQL perspective, this looks trivial - Just write up a new query.

From a Rails perspective, The values you mention are computed values. So if you use find_by_sql, the Model class would not know about the computed fields and hence would return the computed values as strings even if you manage to translate the query into Rails speak. See linked question below.
The general drift (from the responses I got to that question) was to have a separate class be responsible for the rollup / computing the desired values.

How to get rails to return SUM(columnName) attributes with right datatype instead of a string?

Suggestion

Answer 4

A simple way that I used to solve this problem was

In my model I did:

class Article < ActiveRecord::Base
  has_many :posts

  def count_posts
    Post.where(:article_id => self.id).count
  end
end

Now, you can use for example:

Articles.first.count_posts

Im not sure if it can be more efficient way, But its a solution and in my opinion more elegant than the others.

Balliett

When retrieving Articles from articles/index route this will fire one count query for every article returned. If each count takes say, conservatively, 0.25ms and you are asking for 100 Articles, that adds 25ms to the request. So if the request was 5ms before you added this, it is now 30ms - 6 times slower. Elegant but not efficient. – Normalie

Exactly as I said! – Balliett

You did indeed - but I was just illustrating by way of a rough calculation how inefficient it is, so subsequent users can make an informed choice :) – Normalie

Answer 5

I made this work this way:

def show
  section = Section.find(params[:id])
  students = Student.where( :section_id => section.id ).count
  render json: {status: 'SUCCESS', section: students},status: :ok
end

In this I had 2 models Section and Student. So I have to count the number of students who matches a particular id of section.

Naked