Skip to content

Instantly share code, notes, and snippets.

@hukl
Last active June 30, 2020 15:08
Show Gist options
  • Save hukl/d03993d4258c910228beaff68e5f79d3 to your computer and use it in GitHub Desktop.
Save hukl/d03993d4258c910228beaff68e5f79d3 to your computer and use it in GitHub Desktop.

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment