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 

Answer 2

This adds a little overhead, but saves complexity and increases speed later when it matters.

Add a "most_recent" column to books. Make sure you add an index.

class AddMostRecentToBooks < ActiveRecord::Migration    def self.change     add_column :books, :most_recent, :boolean, :default => false, :null => false   end   add_index :books, :most_recent, where: :most_recent  # partial index  end 

Then, when you save a book, update most_recent

class Book < ActiveRecord::Base    on_save :mark_most_recent    def mark_most_recent     user.books.order(:created_at => :desc).offset(1).update_all(:most_recent => false)     user.books.order(:created_at => :desc).limit(1).update_all(:most_recent => true)   end  end 

Now, for your query

class User < ActiveRecord::Base    # Could also include and preload most-recent book this way for lists if you wanted   has_one :most_recent_book, -> { where(:most_recent => true) }, :class_name => 'Book'    scope :last_book_completed, -> { joins(:books).where(:books => { :most_recent => true, :complete => true }) end 

This allows you to write it like this and the result is a Relation to be used with other scopes.

User.last_book_completed