Ecto IN clauses with tuples

Questions / Help
,
17

Late to the party, but I found a hack that builds and expands code at runtime and uses proper parameters instead of interpolation:

def tuple_in(fields, values) do
  fields = Enum.map(fields, &quote(do: field(x, unquote(&1))))
  values = for v <- values, do: quote(do: fragment("(?)", splice(^unquote(Tuple.to_list(v)))))
  field_params = Enum.map_join(fields, ",", fn _ -> "?" end)
  value_params = Enum.map_join(values, ",", fn _ -> "?" end)
  pattern = "(#{field_params}) in (#{value_params})"

  quote do
    dynamic(
      [x],
      fragment(unquote(pattern), unquote_splicing(fields), unquote_splicing(values))
    )
  end
  |> Code.eval_quoted()
  |> elem(0)
end

# usage
from p in Product, where: ^tuple_in([:category_id, :collection_id], [{1, 100}, {2, 200}])

# sql
SELECT
	p0."id",
	p0."title"
# more columns...
FROM
	"product" AS p0
WHERE ((p0."category_id", p0."collection_id")
	IN(($1, $2), ($3, $4));

Using fragment("(?) in (?)", splice(^[p.category_id, p.collection_id]), splice(^values)) does not work because p is out of scope inside of a ^ statement.

My solution uses a dynamic statement and instead creates the field bindings with field/2.

Code.eval_quoted is a bit unorthodox but required to let Ecto
build queries using AST. I hope this helps!

5 Likes