lessless · September 5, 2023 13:51 · Sep 5, 2023
diff --git a/find_non_existing_set.ex b/find_non_existing_set.ex
@@ -0,0 +1,46 @@
+# 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