CODEWITHSUNDEEP
ruby-on-railsactiverecord
O. Frabjous-Dey
O. Frabjous-Dey

Reputation: 1333

Rails ActiveRecord: Counting associations of associations

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:

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?

Upvotes: 0

Views: 644

Answers (2)

scottd
scottd

Reputation: 7474

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.

Upvotes: 2

DanSingerman
DanSingerman

Reputation: 36532

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.

Upvotes: 0

Related Questions