Postgres VALUES in query

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`.

How would one do this join?

1 Like

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
3 Likes

Thanks, I got it working!

    query =
      from(v in Video, preload: [:tags])
      |> join(:left, [v], t in assoc(v, :tags), as: :tags)
      |> join(:left, [v], ids in jsonb_recordset("id int", Enum.map(excluded_ids, fn v -> %{id: v} end)), on: v.id == ids.id, as: :excluded)
      |> where([excluded: ex], is_nil(ex.id))

This looks pretty hacky to me, but at least it’s working.

1 Like

I’ve simplified it even further with unnest:

          query
          |> join(
            :left,
            [v],
            ids in fragment("select unnest(?::int[]) id", ^excluded_ids),
            on: v.id == ids.id,
            as: :excluded
          ) 
         |> where([excluded: ex], is_nil(ex.id))