sql - Rails query through association limited to most recent record?

Question

class User 
has_many :books

I need a query that returns:

Users whose most recent book has :complete => true. i.e. If a user's most recent book has :complete => false, I do not want them in my result.

What I have so far

User.joins(:books).merge(Book.where(:complete => true))

which is a promising start but does not give me the result I need. I've tried adding an .order("created_on desc").limit(1)
to the end of the above query but then I end up with only one result when I am expecting many.

Thanks!

Answer 1

If you aren't going to go with @rubyprince's ruby solution, this is actually a more complex DB query than ActiveRecord can handle in it's simplest form because it requires a sub-query. Here's how I would do this entirely with a query:

SELECT   users.*
FROM     users
         INNER JOIN books on books.user_id = users.id
WHERE    books.created_on = ( SELECT  MAX(books.created_on)
                              FROM    books
                              WHERE   books.user_id = users.id)
         AND books.complete = true
GROUP BY users.id

To convert this into ActiveRecord I would do the following:

class User
  scope :last_book_completed, joins(:books)
    .where('books.created_on = (SELECT MAX(books.created_on) FROM books WHERE books.user_id = users.id)')
    .where('books.complete = true')
    .group('users.id')
end

You can then get a list of all users that have a last completed book by doing the following:

User.last_book_completed