How do I sort a table by the maximum value of associated records?

Question

I have a client model which has many meetings.

class Client < ActiveRecord::Base

  has_many :meetings

end

class Meeting < ActiveRecord::Base

  belongs_to :client

end

I want to produce an ActiveRecord query that will return clients sorted by order of the most recent meeting (as determined by the meeting_time column), but have no idea how to do this. I obviously need to join the tables somehow but I don't know how to generate a suitable subquery in AR. How do I write a join that only includes 1 meeting for each client, in particular the most recent meeting (i.e. the highest value for meetings.meeting_time for a given meetings.client_id). My database is PostgreSQL.

I've encountered similar problems to this previously, struggled with them, and obviously haven't learned a lot from the process. A pointer to a good resource to learn about these sort of situations would also be appreciated.

Answer 1

Something like this?

Client.joins(:meetings).group('clients.id').order('max(meetings.meeting_time) DESC')

This will:

1. Grab clients
2. Join them with meetings
3. Group by the client (id)
4. Now we can use max(meetings.meeting_time) which is the most recent meeting for each client
5. Then sort clients by the most recent meeting time in descending order