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!

right. was trying to make it check :complete => true ? on only the most recent book record for each user but obviously that didnt work... — istan, Mar 09 '11 at 15:27

Pan Thomakos · Accepted Answer · 2011-03-10T21:27:06.460

62

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

edited Mar 10 '11 at 21:27

answered Mar 10 '11 at 17:21

Pan Thomakos

34,082
9
88
85

I was getting an sqlite syntax error "Around 'completed'" when i tried this. Looks promising though! – istan Mar 10 '11 at 20:19
Oh, you might have named your variable 'complete' instead of 'completed'. Simply change the variable name. – Pan Thomakos Mar 10 '11 at 21:18
I updated my answer to use complete instead of completed as well. – Pan Thomakos Mar 10 '11 at 21:27
@pan..should the condition `'books.complete = true' be `'books.complete = 1'` or `'books.complete = ?', true` – rubyprince Mar 11 '11 at 03:46
1

If it's a constant and not user input, it's not necessary to sanitize it with 'books.complete = ?', true. 1 or true should both work as well, at least in MySQL. – Pan Thomakos Mar 11 '11 at 04:34
@pan..oh..I didnt know SQL will give values both for `1` and `true`..i thought Rails was converting `true` or `false` to `1` or `0` while creating the sql query..thanks.. – rubyprince Mar 14 '11 at 04:51
@rubyprince Only SQLite treats 1 and true equivalently -- MySQL and PostgreSQL do not. So for the most portable syntax, you need `'books.complete = ?', true` -- that way Rails' DB adapter can write the query properly for the DB in question. – Marnen Laibow-Koser Sep 19 '12 at 16:21
@MarnenLaibow-Koser..I tried some queries in MySQL, and it seemed it was treating it same. I dont know if it differs in other cases, but for the query in question, both 1 and true were working in the same fashion in MySQL. – rubyprince Sep 20 '12 at 05:58
@rubyprince I had forgotten when I wrote my earlier comment that if the `emulate_booleans` setting is turned on, Rails will treat 1 and 0 like true and false (even if the DB doesn't). I'd advise not doing this, and using a proper boolean field instead: it's usually better to use the right type for the data being represented. – Marnen Laibow-Koser Oct 04 '12 at 20:46
@PanThomakos, SUPER! Works also on polymorphic (`Status` class) with this tweak: `(SELECT MAX(statuses.created_at) FROM statuses WHERE statuses.statusable_id = #{statusable_id} AND statuses.statusable_type = '#{statusable_type}')`. Or maybe 8 years later 8) there is a better query interface for this... – iGian May 30 '19 at 05:40

Mark Swardstrom · Answer 2 · 2016-05-18T02:08:25.430

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

But will have to add something to mark the existing book records as recent (incase database already has some values. — rubyprince, Jan 02 '19 at 18:17

yara · Answer 3 · 2013-11-08T01:11:24.020

I recently came across a similar problem and here is how I solved it:

most_recent_book_ids = User.all.map {|user| user.books.last.id }
results = User.joins(:books).where('books.id in (?) AND books.complete == ?', most_recent_book_ids, true).uniq

This way we only use ActiveRecord methods (no extra SQL) and can reuse it when considering any subset of books for users (first, last, last n books, etc...). You need the last 'uniq' cause otherwise each user would appear twice..

rubyprince · Answer 4 · 2015-07-22T09:43:18.113

2

I cant think of a way to do it in a single query but you can do:

User.all.select { |user| user.books.order("created_at desc").limit(1).complete }

edited Jul 22 '15 at 09:43

answered Mar 09 '11 at 18:57

rubyprince

17,559
11
64
104

1

This is what I ended up implementing but I feel like there *must* be a cleaner way to do it with a db query. It just feels like a simple request to me...I would upvote you but lack the rep :/ – istan Mar 09 '11 at 20:18
1

I know man, my solution is very bad and generates (n + 1) query. But I was not able to find a better solution at that time. that is why I requested @pan to have a look at this question, and he indeed came up with a beautiful and right answer. – rubyprince Mar 13 '15 at 17:51
1

This generate N+1 problem with actually you dont want in your app. – eveevans Feb 18 '16 at 17:19

score 1 · Answer 5 · answered Sep 21 '17 at 18:48

based off Pan's answer but with a left join instead of an inner join, and no grouping:

scope :with_last_book_complete, -> {
  subquery = Book.select(:id)
                 .where("books.user_id = users.id")
                 .order(created_at: :desc).limit(1)

  joins(<<-SQL).where(books: { complete: true })
    LEFT JOIN books
      ON books.user_id = users.id
      AND books.id = (#{subquery.to_sql})
  SQL
}

score 0 · Answer 6 · answered Mar 09 '11 at 15:48

0

you should be using scopes here - they will make your life much simple (this is a rails3 example)

In your book.rb model

scope :completed, where("complete = ?", true)
scope :recent, order("created_on ASC")

Then you can call User.books.recent.completed to get what you want

answered Mar 09 '11 at 15:48

John Beynon

37,398
8
88
97

throws error: undefined method 'books' for # -- I need to query against all users not an instance – istan Mar 09 '11 at 16:04
2

@john..you cant call `.books` on User model..you can call it it on an instance only like `@user = User.first` – rubyprince Mar 09 '11 at 18:36
@rubyprince yep, completely true. you can't. – John Beynon Mar 10 '11 at 09:14
This will not satisfy the OP's criteria: "If a user's most recent book has :complete => false, I do not want them in my result." – Brent Dillingham Jul 11 '16 at 19:34

Rails query through association limited to most recent record?

6 Answers6

Linked