Active Record Join with most recent association object attribute

Question

I have a Contact model which has many Notes. On one page of my app, I show several attributes of a table of contacts (name, email, latest note created_at).

For the note column, I'm trying to write a joins statement that grabs all contacts along with just their latest note (or even just the created_at of it

What I've come up with is incorrect as it limits and orders the contacts, not their notes:

current_user.contacts.joins(:notes).limit(1).order('created_at DESC')

Answer 1

If you just want the created_at value for the most recent note for each contact, you can first create a query to find the max value and then join with that query:

max_times = Note.group(:contact_id).select("contact_id, MAX(created_at) AS note_created_at").to_sql
current_user.contacts.select("contacts.*, note_created_at").joins("LEFT JOIN (#{max_times}) max_times ON contacts.id = max_times.contact_id")

If you want to work with the Note object for the most recent notes, one option would be to select the notes and group them by the contact_id. Then you can read them out of the hash as you work with each Contact.

max_times = Note.group(:contact_id).select("contact_id, MAX(created_at) AS note_created_at").to_sql
max_notes = Note.select("DISTINCT ON (notes.contact_id) notes.*").joins("INNER JOIN (#{max_times}) max_times ON notes.contact_id = max_times.contact_id AND notes.created_at = note_created_at").where(contact_id: current_user.contact_ids)
max_notes.group_by(&:contact_id)

This uses DISTINCT ON to drop dups in case two notes have exactly the same contact_id and created_at values. If you aren't using PostgreSQL you'll need another way to deal with dups.