ecto find_non_existing_set

find_non_existing_set.ex

# courtesy @mrdotb
defmodule T do
  import Ecto.Query
  alias Your.Repo

  @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

  def find_non_existing_set(set_list) do
    pairs = Enum.map(set_list, fn {id, another_id} ->
        %{id: id, another_id: another_id}
      end)

    "pairs"
    |> with_cte("pairs", as: jsonb_recordset("id int, another_id int", pairs))
    |> join(:left, [pair], item in "items", on: item.id == pair.id and item.another_id == pair.another_id)
    |> where([_pair, item], is_nil(item))
    |> select([pair, _item], {pair.id, pair.another_id})
    |> Repo.all()
  end

  def tests do
    find_non_existing_set([{1, 2}, {3, 4}, {1337, 1337}])
    # [{1337, 1337}]
  end
end