Rails ActiveRecord: Counting associations of associations

Question

I'm trying to write an ActiveRecord find query with no success. Please help!

This feature of my project is kind of a blog with Digg-like features: each registered user can add a star to the blog post itself or any of its replies.

So here are the relevant model classes:

• BlogPost (has_many Replies)
• Reply (belongs_to Blog Post)
• Starring (this represents the action of one users starring one BlogPost or Reply; it has a polymorphic interface to BlogPost and Reply, referring to them internally as :starrables, replete with :starrable_id and :starrable_type fields)

So, what I am trying to do is write a method in blog_post.rb that will return the total number of starrings for the BlogPost object as well as all of its Reply classes. That is, if a blog post has 10 stars, and it has two replies which each have 5 stars, it should return 20.

How do I write this query without making a jillion database transactions?

Answer 1

Assuming you have a polymorphic method stars on both the BlogPost and Reply that returns the Starring objects for each (you may not, but it makes sense to me that you should) then in blog_post.rb you can do:

def total_number_of_stars
  stars.size + replies.inject(0) { |s,v| s.stars.size += v.stars.size }
end

Admittedly this will do quite a few queries behind the scenes, but that's kind of how Rails works. If it does not perform well enough you can use find_by_sql with bespoke SQL.

Answer 2

I would suggest doing it with SQL. You can do it several ways. Are you using MYSQL? If you want to do with with 2 queries and then add the numbers together you can do something like this in SQL:

select count(starrings.id) from starrings where starrable_type='BlogPost' and starrable_id=#{blogpost.id}

and

select count(starrings.id) from starrings join replies on starrings.starrable_type='Reply' and starrable_id=replies.id and replies.blog_post_id=#{blogpost.id}

Note: I didn't test this SQL and may have misspelled something or made a typo.

You can run SQL counts with 'Starring.count_by_sql(your sql here)' then add the 2 numbers together. You can probably get this down to 1 SQL statement with a union, but I wouldn't bother.