CODEWITHSUNDEEP
sqlruby-on-rails
Abram
Abram

Reputation: 41874

Providing a join count and total count in the same query with Active Record

One style has many reviews.

Let's say I am doing: 

Style.select('styles.id').
    joins(:reviews).
    select('reviews.style_id, count(reviews.id) AS review_count')

... but I also want to know the total number of reviews! Is it possible to pull that information as well. I tried count(*) as total_reviews, but this still returns the review count per style.

Upvotes: 0

Views: 110

Answers (1)

rewritten
rewritten

Reputation: 16435

If you think in SQL, you are building up a lot of rows (well, a subset of that lot), and in each row you are putting a count for that row.

On the other side, you want a total count. Which of the retrieved records should have that total and in which column/attribute?

It's not that you can't do that in rails, it's that you can't do that in SQL itself. The resultsets are completely different, so you need two queries, so you need two ActiveRecord calls.

A different way to approach this (again you need two separaate queries, but they are simpler) is to use a counter_cache for @style.reviews:

# add a 'reviews_count' integer column to the 'styles' table and use it as 
# a counter cache as follows
class Review < AR::Base
  belongs_to :style, :counter_cache => true
end

Now you have the counts without the need of a join, and the total count can be obtained with a simple sum.

Addendum: To use the ocounter cache, query the styles table as always (without includes or joins), and call

@style.reviews.size

whenever you want to get the count of the reviews. This won't make another query, instead will use the cached counter on the @style instance itself.

Upvotes: 1

Related Questions