CODEWITHSUNDEEP
ruby-on-railsactiverecord
Davor
Davor

Reputation: 1237

Rails 4: order model by latest date of child

In Rails 4, I have a model Thread which has_many Emails. Each Email has a field named internal_date. I want to return a collection of threads, ordered in a way where the thread with the latest email.internal_date comes first (very similar to how Gmail would sort its inbox).

This is the current line in my controller (not ordering them so far):

@threads = selected_threads.joins(:tags).filter(params_filters).includes(:emails, [some other stuff]).distinct.all.paginate(page: params[:page], :per_page => 10)

I'm doing the joins because of the filtering; and using includes to speed things up.

Ideally I would add a scope order_by_latest_email to my Thread model, without killing the loading time with too many DB queries. Any tips?

Thanks!

Upvotes: 1

Views: 382

Answers (1)

ptd
ptd

Reputation: 3053

I think this is only possible with a really ugly query like so:

Thread.joins(:emails)
  .select('threads.*, emails.internal_date')
  .joins('LEFT OUTER JOIN emails em ON (emails.internal_date < em.internal_date and emails.thread_id = em.thread_id)')
  .where('em.id IS NULL').order('emails.internal_date DESC')
  # additional filters here

You can see details in a blog post here, but this is a semi common sql problem known as the greatest n per group.

You need to find the latest email internal date in the group of emails connected to a thread. So what you do is:

  1. compare all the emails with each other
  2. continue until you find one where no other email (where em represents that other email) has a later internal_date (that's what the em.id IS NULL is doing)
  3. order by that email's internal_date.

Upvotes: 2

Related Questions