sql - activerecord 中最近的内部查询

Question

我有一张 table users :

id  first_name
--------------
1   Bill
2   Denise

谁阅读了多个 books :

id  book                       user_id  read_at
---------------------------------------------------
1   Garry Potter               1        2020-1-1
2   Lord of the wrist watch    2        2020-1-1
3   90 Shades of navy          2        2020-1-2

我想在我的 book 中创建一个范围为每个用户提供最新书籍的模型。有很多使用纯 SQL 执行此操作的示例，我遇到的问题是创建一个灵活的范围，该范围可以与计数、内部查询或您通常使用范围的任何其他方式一起使用。

到目前为止，我的 book 中有这个模型:

def self.most_recent
  inner_query = select('DISTINCT ON (user_id) *').order(:user_id, read_at: :desc)
  select('*').from(inner_query, :inner_query).order('inner_query.id')
end

这非常接近我想要的。它适用于计数，但不适用于更复杂的情况。

例如，如果我想获取他们最新一本书是“加里·波特”的用户列表，我会尝试以下操作:

User.where(id: Book.most_recent.where(book: 'Garry Potter').select(:user_id))

事件记录被混淆并生成此 SQL:

SELECT "users".* FROM "users" WHERE "users"."id" IN (SELECT "user_id", * FROM (SELECT "books"."user_id", DISTINCT ON (user_id) * FROM "books" ORDER BY "books"."user_id" ASC, "books"."read_at" DESC) inner_query WHERE "books"."book" = "Garry Potter" ORDER BY inner_query.id)

这给出了以下错误:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR:  syntax error at or near "DISTINCT"

有没有一种优雅的方法来实现这一目标？

Answer 1

您可以尝试更改方法 most_recent通过返回带有 where 的查询:

def self.most_recent
  # Select only the ids of the most recent books
  inner_query = select('DISTINCT ON (user_id) books.id').order(:user_id, read_at: :desc)

  # Return a where query like Book.where(id: <ids in the result set of the query above>)
  where(id: inner_query)
end

# You should now be able to perform the following query
User.where(
  id: Book.most_recent.where(book: 'Garry Potter').select(:user_id)
)