ALL query in Ecto

Question

I need to translate the following SQL to Ecto Query DSL.

SELECT otc.*
FROM users u
INNER JOIN one_time_codes otc ON u.id = otc.user_id
LEFT OUTER JOIN one_time_code_invalidations otci ON otci.one_time_code_id = otc.id
WHERE u.id = 3 AND
      otc.code = 482693 AND
      otci.inserted_at IS NULL AND
      otc.inserted_at > all(SELECT otc.inserted_at
                            FROM one_time_codes otc2
                            WHERE otc2.user_id = 3) AND
      otc.inserted_at > (now() - '180 seconds'::interval);

In the third AND statement of WHERE clause, notice there is an ALL query. Ecto seems to not have corresponding function in Ecto.Query.API.

How to apply such aggregate? What is the correct way to implement this, though it could be implemented by having a lookup on debug logs of the Ecto, do you have another idea (or suggestion)?

Thank you.

Answer 1

Ecto does not allow subqueries in expressions, and you're right that there's no all in Ecto's Query API, but you can use fragment like this:

where: ...
  and otc.inserted_at > fragment("all(SELECT otc.inserted_at FROM one_time_codes otc2 WHERE otc2.user_id = ?", 3)
  and ...