ActiveRecord query, order by association, last of has_many

Question

Im trying to list all Users by the created_at column of the most recently created associated recored (communications).

What I have so far:

User.includes(:communications).order(
  'communications.created_at IS NULL, communications.created_at asc'
)

As it is, desc works as I expect. The issue is when the order is reversed and I try order asc. It appears that is's because the user can have many communications, The query returns the list of users in order of the first communications created instead of the most recent.

How can I modify the query to target the most recently created associated records in both asc and desc order?

Thanks for you time.

Answer 1

Problem is you are trying to order the parent by child's attributes, so your solution will work only when their orders have the same direction.

The way to work with it is using aggregate function for the children's attribute like this:

# ascending
User
  .joins('LEFT JOIN communications ON communications.user_id = users.id')
  .group('users.id')
  .order('MAX(communications.created_at) ASC')

# descending
User
  .joins('LEFT JOIN communications ON communications.user_id = users.id')
  .group('users.id')
  .order('MAX(communications.created_at) DESC')