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.
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.
Depending on the size and complexity of the database, it may be sometimes better to do two queries instead of joins on tables. In case you want to skip joins one way could be to get the order of Ids from one select query, and then retrieving the records from the second query.
ordered_user_ids = Appointments.select('user_id, count(1) AS N')
.where(:type => 'typeB').group(:user_id).order('N desc').collect(&:user_id)
# find users keeping the order of the users intact
User.find(ordered_user_ids, :order => "field(id, #{ordered_user_ids.join(',')})")
