gistfile1.md

I want to do an IN query by providing a specific order from a previous query

Desired SQL Query

SELECT * 
FROM fnords
INNER JOIN   unnest('{9026, 9025, 9024, 9021, 9020, 9019}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
WHERE id IN (9026, 9025, 9024, 9021, 9020, 9019)
ORDER BY t.ord;

Attempted Ecto DSL Equivalent

from(
  v in Fnord, 
  select: [:id], 
  where: v.id in [9026, 9025, 9024, 9021, 9020, 9019], 
  join: t in fragment("unnest('{9026, 9025, 9024, 9021, 9020, 9019}'::int[]) WITH ORDINALITY t(id, ord) USING (id)"), 
  order_by: t.ord
) |> Repo.all

Resulting Query which has a syntax error

SELECT f0."id" 
FROM "fnords" AS f0 
INNER JOIN unnest('{9026, 9025, 9024, 9021, 9020, 9019}'::int[]) WITH ORDINALITY t(id, ord) USING (id) AS f1 ON TRUE 
WHERE (f0."id" IN (9026,9025,9024,9021,9020,9019)) ORDER BY f1."ord"

Another but less desirable way would be

from(
  f in Fnord, 
  select: [:id], 
  where: f.id in [9026, 9025, 9024, 9021, 9020, 9019], 
  order_by: fragment("array_position(ARRAY[9025, 9026,9024, 9021, 9020, 9019]::bigint[], id::bigint)")
) |> Repo.all

This works in iex but in the code it is forbidden to put that string directly into fragment() due to possible SQL injection. It does not work as a prepared statement though because array_position() is a dynamic function

Ideally I'd like to translate the first query though.

Any ideas?