How to perform COUNT with HABTM and left join?

Question

Considering the following associations:

class Pool < ActiveRecord::Base
  has_and_belongs_to_many :participations
end

class Participation < ActiveRecord::Base
  has_and_belongs_to_many :pools
end

I want to get the number of participations in each pools (even if there is no participation).

This is what I am expecting (id is pool id):

+----+----------------------------+
| id | count('participations.id') |
+----+----------------------------+
| 1  | 1                          |
| 2  | 0                          |
| 3  | 0                          |
| 4  | 0                          |
| 5  | 0                          |
| 6  | 0                          |
| 7  | 0                          |
| 8  | 0                          |
+----+----------------------------+

This is what I get:

+----+----------------------------+
| id | count('participations.id') |
+----+----------------------------+
| 1  | 3                          |
| 2  | 1                          |
| 3  | 1                          |
| 4  | 1                          |
| 5  | 1                          |
| 6  | 1                          |
| 7  | 1                          |
| 8  | 1                          |
+----+----------------------------+

To obtain this result, I do a left join with a group by and a count:

Pool.joins('LEFT JOIN participations_pools ON participations_pools.pool_id = pools.id LEFT JOIN participations ON participations.id = participations_pools.participation_id').select("pools.id, count('participations.id')").group('pools.id')

I don't know how to get the good result and why I get that?

EDIT:

My answer at my question:

Pool.joins('LEFT JOIN participations_pools ON participations_pools.pool_id = pools.id LEFT JOIN participations ON participations.id = participations_pools.participation_id').select("pools.id, count(participations.id)").group('pools.id')

The quotes around count are the cause of my troubles

cristian · Accepted Answer · 2014-06-15T12:18:21.697

If you don't want to worry about that, write your query using only ActiveRecord methods:

 Pool.joins('LEFT JOIN participations_pools ON participations_pools.pool_id = pools.id')
   .joins('LEFT JOIN participations ON participations.id = participations_pools.participation_id')
   .group('pools.id').count('participations.id')

The result will be a hash having pools.id as a key and count('participations.id') as a value for each row extracted from your database.

More info for count method: http://api.rubyonrails.org/classes/ActiveRecord/Calculations.html#method-i-count

If count is used with group, it returns a Hash whose keys represent the aggregated column, and the values are the respective amounts:

How to perform COUNT with HABTM and left join?

1 Answers1