通过仅限于最近记录的关联的 Rails 查询? [英] Rails query through association limited to most recent record?

问题描述

class User 
has_many :books

我需要一个返回的查询:

I need a query that returns:

最近一本书具有 :complete => true 的用户.即如果用户最近的书有 :complete => false，我不希望它们出现在我的结果中.

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.

到目前为止我所拥有的

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

这是一个有希望的开始，但没有给我想要的结果.我试过添加一个 .order("created_on desc").limit(1)
到上述查询的结尾，但是当我期待很多结果时，我最终只得到一个结果.

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.

谢谢！

推荐答案

如果你不打算使用@rubyprince 的 ruby​​ 解决方案，这实际上是一个比 ActiveRecord 可以处理的更复杂的数据库查询，因为它需要一个子查询.以下是我将如何通过查询完全做到这一点:

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

要将其转换为 ActiveRecord，我将执行以下操作:

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

这篇关于通过仅限于最近记录的关联的 Rails 查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！