I need to do an anti-join to exclude some records by their ids:
select * from tbl
left outer join (values (1), (2), ...) as excluded on tbl.id = excluded.column1 where excluded.column1 is null
AFAIK, this is the fastest way (in terms of performance: postgresql - Postgres NOT IN performance - Stack Overflow) to do that. However, I’m unable to replicate this query with Ecto. I’ve tried to generate the (VALUES (1), (2)) string using:
excluded_ids_query = Enum.map_join(excluded_ids, ",", fn v -> "(#{v})" end) |> (fn x -> "(VALUES #{x})" end).()
query =
from(v in Model)
|> join(:left, [v], ids in fragment(excluded_ids_query), on: v.id == ids.column1)
But this code produces an error about potential SQL injection:
(ArgumentError) to prevent SQL injection attacks, fragment(...) does not allow strings to be interpolated as the first argument via the `^` operator, got: `excluded_ids_query`.
Ecto doesn’t support such queries atm, at least not with runtime values. Though I’m wondering if you could join a jsonb_to_recordset, which would allow you to pass the list of values as a single parameterized list of values:
@doc """
Use runtime data as an adhoc db table to join to data.
`types` need to be a compile time string literal.
`values` can be runtime supplied.
## Example
data = [%{id: 1, text: "hey"}, %{id: 2, text: "ho"}]
from a in Table,
join: b in jsonb_recordset("id uuid, text text", data),
on: a.id == b.id
"""
defmacro jsonb_recordset(types, values) do
quote do
fragment(
unquote("(SELECT * FROM json_to_recordset(?) as t(#{types}))"),
^unquote(values)
)
end
end