Order model by association attributes

Question

I have an Event model which has many EventDates.

An EventDate has a start_time attribute and an Event has a next_occurrence method which finds the EventDate belonging to it with the nearest start_time.

I want to find the first 5 Events ordered by next_occurrence (soonest first).

I thought I might be able to achieve that with something like this:

Event.joins(:event_dates).order('event_dates.start_time').limit(5)

but this isn't right - if I then collect the next_occurrence dates, they are out of order.

How could I query this to return Events ordered by next_occurrence?

Answer 1

Try this:

next_5_events = EventDate.select('event_id, min(date) as next_event_date')
                         .includes(:event)
                         .where('next_event_date > ?', Time.now)
                         .group(:event_id)
                         .order(:next_event_date)
                         .limit(5)
                         .map(&:event)

Explanation:

Find for each Event the nearest EventDate in the future

EventDate.select('event_id, min(date) as next_event_date')
         .where('next_event_date > ?', Time.now)
         .group(:event_id)

Keep only the 5 first events ordered by the next_event_date

         .order(:next_event_date)
         .limit(5)

As is, the request would return only the event_id and the next_event_date, so the following lines eager load and return the Event objects

         .includes(:event)
         .map(&:event)