Rails & Active Record: Create a scope to get the most recent child

Question

A User has_many Books. I want to add a scope to Books that only returns the most recent (created_at) book for each user_id. Something that groups the books by user_id and only gets the most recent for each user_id ?

To clarify, I could technically do this:

User.all.each { |user| user.books.first }

But I'd like to do it with a clean Active Record query.I'm having a lot of trouble figuring it out, but I imagine the solution isn't too complicated.

Answer 1

The easiest way to do this is to create a scope on books that, using a subquery, only selects books that are the latest ones owned by their user:

class Book < ActiveRecord::Base
  belongs_to :user

  scope :most_recent, -> {
    where('NOT EXISTS(SELECT 1 FROM books b2 WHERE b2.user_id = books.user_id AND b2.created_at > books.created_at)')
  }
end

This is just one of many options, but it's likely the most re-usable as you can join this relation onto others without worrying about your aggregations conflicting.