Count by association: syntax error at or near "AS"

Question

I'm trying to order a group of TV shows by how many times an actor has been on. I followed some other stack overflow questions asking the same question but I'm getting an error I don't see mentioned anywhere.

 Show.joins(:contributions)
  .select('show.*, COUNT(contributions.id) AS guest_count')
  .where('contributions.role_id = 2')
  .where('contributions.person_id IN (?)', self.id)
  .order('guest_count desc')

PG::SyntaxError: ERROR: syntax error at or near "AS" LINE 1: SELECT COUNT(show.*, COUNT(contributions.id) AS guest_co...

Output:

: SELECT COUNT(COUNT(contributions.id) AS guest_count, show.*) FROM
"show" INNER JOIN "episodes" ON "episodes"."show" = "show"."id" 
INNER JOIN "contributions" ON "contributions"."episode_id" = 
"episodes"."id" WHERE (contributions.role_id = 2) AND 
(contributions.person_id IN (42))

This is the stack overflow I've been following: Rails 3 ActiveRecord: Order by count on association

Answer 1

It looks like arel isn't setting up your SQL statement correctly. Try reversing the conditions in the select.

.select('COUNT(contributions.id) AS guest_count, show.*')

Maybe that will help arel out.

EDIT:

You also have a second problem. You're trying to use an aggregate function but you're not indicated which columns you want to group by. When using the aggregate don't use show.* and instead list the columns you want to select. After, use the group method to list the columns so they can be included in the aggregate query.

EDIT 2:

Can you just use something like:

shows = Show.includes(:contributions).all

and access the counts with:

shows.first.contributions.count

or is this a performance issue you're optimizing?

Answer 2

The raw SQL could look like this:

SELECT s.*, x.ct
FROM   shows s
LEFT JOIN (
   SELECT e.show, count(*) AS ct
   FROM   episodes e
   JOIN   contributions c ON e.id = c.episode_id
   WHERE  c.role_id = 2
   AND    c.person_id = ?  -- self.id
   GROUP  BY 1
   ) x ON s.id = x.show
ORDER  BY x.ct DESC NULLS LAST;

This includes shows without any appearances of the person, but takes care to sort those last. To exclude those shows use JOIN instead of LEFT JOIN.

Consider:

• GroupingError: ERROR: column " " must appear in the GROUP BY clause or be used in an aggregate function
• Nested query in squeel
• PostgreSQL sort by datetime asc, null first?

Answer 3

You have to change show tables into shows:

Show.joins(:contributions)
  .select('shows.*, COUNT(contributions.id) AS guest_count')
  .where('contributions.role_id = 2')
  .where('contributions.person_id IN (?)', self.id)
  .group("shows.id")
  .order('guest_count desc')