Rails query through association limited to unique associated objects?

Question

class User 
has_many :books

I need a query that returns:

Three most recent books belonging to unique users. I.e. if my DB has

users:
  id: 1, name: "John"
  id: 2, name: "Mary"
  id: 3, name: "Bob"
books: 
  id: 1, title: "Ender's Game", user_id: 1
  id: 2, title: "Dune", user_id: 1
  id: 3, title: "The Martian", user_id: 1
  id: 4, title: "The Shining", user_id: 2
  id: 5, title: "The Call of Cthulhu", user_id: 2
  id: 6, title: "The Hobbit", user_id: 3

I expect the query to return books with id: [3, 5, 6] as those are the last books of each respective user.

So far I have

Book.joins(:user).where( ... ).order(created_at: :desc).first(3)

The placeholder .where is where the actual hard part would happen.

I am aware I could return the needed objects using a ruby loop, but perhaps there is a scope-friendly solution?

Answer 1

You may use a subquery:

books = Book.where(id: Book.select('MAX(id) AS id').group(:user_id)).order(created_at: :desc)