victorolinasc

Ecto IN clauses with tuples

Hello people!

I have a requirement to perform a select query with IN clause using tuples. I am using PostgreSQL. Example:

select * from my_table where (col1, col2, col3) in ((1, 2, 3), (4, 5, 6), (7, 8, 9));

This is valid SQL that we can’t, currently, express with Ecto. Tried with several interpolation techniques but was not successful. The list of values is dynamic in content and length (can’t use a fragment here).

We are falling back to a Repo.query! (making sure this is not a public query filled with user provided data) but would like to know if anyone can think of a way to keep it in Ecto.Query API (mostly for the added readability and security).

Thanks in advance!

20 comments

#ecto #ecto-query

3 5699 20

2025-09-23 02:30:11 UTC

Most Liked

dli

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!

Post #17

alex_weinberger

Here is an example of both methods, the array per field and the jsonb_to_recordset, using ecto.

Array per field + unnest:

ids =[ 1,  2,  1]
ages=[10, 20, 30]

from x in Friends.Person, 
inner_join: j in fragment("SELECT distinct * from unnest(?::int[],?::int[]) AS j(id,age)", ^ids, ^ages),
        on: x.id==j.id and x.age==j.age,
select: [:name]

jsonb_to_recordset:

list = [%{id: 1, age: 10}, 
        %{id: 2, age: 20}, 
        %{id: 1, age: 30}]

from x in Friends.Person,
inner_join: j in fragment("SELECT distinct * from jsonb_to_recordset(?) AS j(id int,age int)", ^list),
        on: x.id==j.id and x.age==j.age,
select: [:name]

Post #16

benwilson512

Author of Craft GraphQL APIs in Elixir with Absinthe

That isn’t logically equivalent. Suppose you have WHERE (col1, col2) IN ((1, 10), (2, 20)). If you do WHERE col1 in (1, 2) AND col2 IN (10, 20) that would allow a row that had col1 as 1 and col2 as 20, which would not have been allowed by the first query.

@victorolinasc I’ve had some success using unnest and JOIN for here’s an example:

    SELECT p.result_order as result_order, si.*
      FROM unnest($1::text[], $2::timestamp[], $3::timestamp[], $4::integer[])
      AS p(slug, starts_at, ends_at, result_order)
    LEFT JOIN sensors AS s ON s.slug = p.slug
    LEFT JOIN sensor_installations AS si
      ON s.id = si.sensor_id
      AND (si.activated_at, COALESCE(si.deactivated_at, p.ends_at + INTERVAL '5 minutes')) OVERLAPS
          (p.starts_at, p.ends_at)
    ORDER BY p.result_order ASC

The nice thing is that you get to at least use parameterized queries instead of interpolation, and if you join on multiple columns it should be perfectly able to use compound indices. The downside of course is that you’re still writing raw SQL. This is a pretty old query for us, you might be able to write the unnest as raw SQL but then use that as a subquery in a regular ecto query. Not sure.

Post #6

Last Post!

gabrielgiordano

For anyone looking on this, I’ve added correct type support:

  @doc """
  Builds a row constructor IN query for composite keys, also known as tuple IN query.

  It outputs `(id, name) in ((1::bigint, "Company A"::varchar), (2::bigint, "Company B"::varchar))`.

  ## Example

      iex> from(s in schema, where: ^tuple_in(schema, [:id, :name], [[1, "Company A"], [2, "Company B"]]))

  """
  @spec tuple_in(Ecto.Schema.t(), [atom()], [[any()]]) :: Ecto.Query.dynamic_expr()
  def tuple_in(schema, fields, values) when length(fields) > 0 and length(values) > 0 do
    types = Enum.map(fields, &schema.__schema__(:type, &1))

    quoted_fields =
      Enum.map(fields, fn field ->
        quote do
          field(s, ^unquote(field))
        end
      end)

    quoted_values =
      Enum.map(values, fn values ->
        quoted_type_values =
          values
          |> Enum.zip(types)
          |> Enum.map(fn {value, type} ->
            quote do
              type(^unquote(Macro.escape(value)), unquote(type))
            end
          end)

        params = "(#{Enum.map_join(quoted_type_values, ",", fn _ -> "?" end)})"

        quote do
          fragment(unquote(params), unquote_splicing(quoted_type_values))
        end
      end)

    params =
      "(#{Enum.map_join(quoted_fields, ",", fn _ -> "?" end)}) in (#{Enum.map_join(values, ",", fn _ -> "?" end)})"

    {term, _bindings} =
      Code.eval_quoted(
        quote do
          dynamic(
            [s],
            fragment(unquote(params), unquote_splicing(quoted_fields), unquote_splicing(quoted_values))
          )
        end
      )

    term
  end

  def tuple_in(_schema, _fields, _values), do: dynamic(false)

Post #21