Joins and select count by conditions

Question

I am trying to get top 5 movies that have the most purchases. So I came up with this query:

@movies = Movie.joins(:purchases)
          .select('movies.id, movies.name, count(purchases.id) as purchases_count')
          .group('movies.id').order('purchases_count desc').limit(5)

Then I display the top 5 movies in my view along with their purchase type count(purchases have 2 types, "partial" or "full").

<% @movies.each do |t| %>
<%= t.name %>
Partial purchase:
<%= t.purchases.where(type: "partial").count %>
Full purchase:
<%= t.purchases.where(type: "full").count %>
<% end %>

This is working, however, I am afraid this causes N+1 queries due to select count from movies. Some solutions I can think of includes:

1) Is there a way to filter purchase count in select method? for example something like this:

@movies = Movie.joins(:purchases)
          .select('movies.id, movies.name, count(purchase.where(type: "partial")) as purchases_partial_count, count(purchases.id) as purchases_count')

2) Can I extract the ids from @movies and write another query such as: @purchases = Movie.find_by(@movies_ids).includes(:purchases) and use length to replace count in my views?

I am open to other better solutions, thanks and any help will be appreciated!

score 4 · Accepted Answer · edited May 23 '17 at 10:33

4

You could do it by writing a little bit more custom SQL almost like you suggest as alternative 1 and using SUM CASE SQL sum with condition

@movies = Movie.joins(:purchases)
      .select('movies.id, movies.name, count(purchases.id) as purchases_count, 
      SUM(CASE WHEN purchases.type = "partial" THEN 1 ELSE 0) as partial_count,
      SUM(CASE WHEN purchases.type = "full" THEN 1 ELSE 0) as full_count')
      .group('movies.id').order('purchases_count desc').limit(5)

edited May 23 '17 at 10:33

Community

1
1

answered May 02 '16 at 06:34

Albin

2,912
1
21
31

Thanks. But I got this error - `ActiveRecord::StatementInvalid: PG::UndefinedColumn: ERROR: column "partial" does not exist`, wondering why it is looking for `"partial"` as a column.. – pyfl88 May 02 '16 at 07:45
I think it might be bad qouting on my part. I am a bit uncertain but try with ssingle quotes. – Albin May 02 '16 at 08:54
Yes, based on this article it should be singlequotes: http://stackoverflow.com/questions/17811673/sql-query-sumcase-when-x-then-1-else-0-for-multiple-columns – Albin May 02 '16 at 08:56

score 0 · Answer 2 · answered Feb 06 '18 at 07:19

It should be

@movies = Movie.joins(:purchases).select('movies.id, movies.name, count(purchases.id) as purchases_count, SUM(CASE WHEN purchases.type = "partial" THEN 1 ELSE 0 END) as partial_count,SUM(CASE WHEN purchases.type = "full" THEN 1 ELSE 0 END) as full_count').group('movies.id').order('purchases_count desc').limit(5)

Joins and select count by conditions

2 Answers2