Question: List the number of records that have X number of associations in has_many

Question

List the number of records that have X number of associations in has_many

Answers 2
Added at 2017-01-04 20:01
Tags
Question

I have my Person class which will have 0 or more widgets associated

class Person < ActiveRecord::Base
  has_many :widgets
end

I'd like to list out the number of People that have X widgets i.e:

Num of widgets | Num of people
0              | 10
1              | 34
2              | 0
...
100            | 20

(which is saying 34 people have 1 widget, etc).

Here's my best effort so far:

Person.joins(:widgets).group('people.id').select('people.id, count(widget_id) as widget_count').group('widget_count')

But I receive

 Mysql2::Error: Can't group on 'widget_count'
Answers
nr: #1 dodano: 2017-01-04 20:01

I can't help you very much with the syntax as this is not my strong suite. But knowing SQL you might want to swap the count around. What you are counting are actually the people.id's.

Hopefully this helps your line of thought.

nr: #2 dodano: 2017-01-04 21:01

The raw SQL query should look like this (tested in PostgreSQL):

SELECT a.widget_count, COUNT(a.widget_count) FROM 
(SELECT p.id, COUNT(w.id) AS widget_count FROM Person p JOIN Widget w ON w.person_id = p.id GROUP BY p.id) 
AS a 
GROUP BY a.widget_count
Source Show
◀ Wstecz