Order by count of a model's association with a particular attribute
activerecord, postgresql, ruby, ruby-on-rails
Solution
With `joins` (INNER JOIN) you'll get only those users, who have at least one appointment associated:
User.joins(:appointments)
.where(appointments: { type: 'typeB' })
.group('users.id')
.order('count(appointments.id) DESC')
If you use `includes` (LEFT OUTER JOIN) instead, you'll get a list of all users having those without appointments of `'typeB'` at the end of the list.
Problem
Say I have the two models `Users` and `Appointments` where a user has_many appointments. An appointment can be of two different types: `typeA` and `typeB`. How can I write a query to order the users by the amount of `typeB` appointments they have? I've looked into counter_cache but it seems to just count the number of the association (so in this case the number of appointments a user would have) and does not allow for the counting of a particular type of appointment.