Providing subquery in query parameters

Question

I need to implement the following query with Ecto, however it doesn't compile saying the subquery statement is not a valid expression.

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.

The equivalent PgSQL query would be something like:

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)

Is there a way to implement that, or am I missing something?

Answer 1

As of Ecto 2.1.0, according to https://hexdocs.pm/ecto/Ecto.Query.html#subquery/1, subqueries cannot be use in where:

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

You can use fragment and put the entire subquery in it for now:

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

Answer 2

where just became available (Ecto 3.4.3)

https://hexdocs.pm/ecto/Ecto.Query.html#subquery/2