postgresql - 在查询参数中提供子查询

Question

我需要用 Ecto 实现以下查询，但是它没有编译说子查询语句不是有效的表达式。

query = from p in Passphrase,
        left_join: pi in PassphraseInvalidation, on: p.id == pi.target_passphrase_id,
        join: u in User, on: p.user_id == u.id,
        where: p.passkey == ^passkey and
               is_nil(pi.inserted_at) and
               p.inserted_at > ago(5, "month") and
               p.inserted_at > subquery(from pr in PasswordReset,
                                        where: pr.user_id == u.id,
                                        select: max(pr.inserted_at)),
        select: {u, p}

user = Repo.one!(query)

(Ecto.Query.CompileError) subquery(from(pr in PasswordReset, where: pr.user_id() == ^u.id(), select: max(pr.inserted_at()))) is not a valid query expression.

等效的 PgSQL 查询类似于:

SELECT u.*, p.*
FROM passphrases p
LEFT OUTER JOIN passphrase_invalidations pi ON p.id = pi.target_passphrase_id
INNER JOIN users u ON p.user_id = u.id
WHERE p.passkey = '/* some passkey */' AND
      pi.inserted_at IS NULL AND
      u.id = 2 AND
      p.inserted_at > (SELECT max(pr.inserted_at)
                       FROM password_resets pr
                       WHERE pr.user_id = u.id)

有没有办法实现它，还是我遗漏了什么？

Answer 1

自 Ecto 2.1.0 起，根据 https://hexdocs.pm/ecto/Ecto.Query.html#subquery/1 , 子查询不能在 where:

中使用

Subqueries are currently only supported in the from and join fields.

您现在可以使用 fragment 并将整个子查询放入其中:

p.inserted_at > fragment("(SELECT max(pr.inserted_at) FROM password_resets pr WHERE pr.user_id = u.id)")