How to sort a table by the maximum value of related records?

Question

I have a client model that has many meetings.

class Client < ActiveRecord::Base has_many :meetings end class Meeting < ActiveRecord::Base belongs_to :client end 

I want to create an ActiveRecord request that will return clients sorted in order of the most recent meeting (as defined by the meeting_time column), but I don’t know how to do this. I obviously need to somehow join the tables, but I don't know how to create a suitable subquery in AR. How to write a connection that includes only 1 meeting for each client, in particular the most recent meeting (that is, the highest value for meeting.meeting_time for this meeting.client_id). My database is PostgreSQL.

I faced similar problems earlier, struggled with them and, obviously, did not learn much in this process. A pointer to a good resource will also be appreciated to learn about these situations.

Answer 1

Something like that?

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

It will be:

• Grab customers
• Join them with meetings
• Group by customer (id)
• Now we can use max(meetings.meeting_time) , which is the most recent meeting for each client
• Then sort the customers by the most recent meeting time in descending order.