Translating SQL query with row constructors (?) into Ecto

[khustochka]

Hi all,

I have a table books with columns name and version. I want to search for books matching one of the provided pairs of (name, version). The SQL query looks like this:

select * from public.books
where (name, version) in (('ruby', 'v1'), ('ruby', 'v2'), ('elixir', 'v1'))

What I have tried:

signatures = [{"ruby", "v1"}, {"ruby", "v2"}, {"elixir", "v1"}]
from(b in Book,
  where: fragment("(?, ?) IN (?)", b.name, b.version, ^signatures)
)

This fails with:

** (DBConnection.EncodeError) Postgrex expected a tuple, got [{“ruby”, “v1”}, {“ruby”, “v2”}, {“elixir”, “v1”}]. Please make sure the value you are passing matches the definition in your table or in your query or convert the value accordingly.

Well, I tried a few more options, but they made even less sense. I feel like such a simple expression should be possible to express in Ecto?

(I am not even sure how this constructions (col1, col2, …) are called in SQL but I suspect this is row constructors, because I can replace my query with select * from public.books where ROW(name, version) in (ROW('ruby', '1'), ROW(...), ...)) and it works the same.

Thanks!

[linusdm]

This can be accomplished with the values query API. Ecto.Query.API — Ecto v3.14.0
You can join with the values using the name and version column.

[al2o3cr]

This comes up from time to time, but I haven’t seen a “great” solution for it. Here’s some previous threads you might find relevant:

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 quer…

Multi-valued WHERE clause, ie `WHERE (col_a, col_b) > (val_a, val_b)`

I can’t seem to figure out how to build a multi-valued WHERE clause dynamically. End goal is deep pagination with sub-sorts with dynamic sort field & sort order. I’m stuck at generating the fragment for the WHERE (t.inserted_at, t.id) > (?, ?) part of the query. def start_at(query, fields, values) do where(query, [table: t], (for f <- fields, do: field(t, ^f)) > (for v <- values, do: ^v)) end I’d like to have start_at(query, [:inserted_at, :id], ["2020-10-01 12:11:10", 1234]) generate the …

[Schultzer]

You might even be interested in not translating at all and just implementing Enumerable on top of GitHub - elixir-dbvisor/sql: SQL provides state-of-the-art, high-performance SQL integration for Elixir, built to handle extreme concurrency with unmatched expressiveness and ergonomic query composition. Write safe, composable, parameterized queries directly, without translating to Ecto or any ORM. · GitHub it’s still in active development but main is conformant to SQL 2016

[khustochka]

Thanks for the links to the past threads!

I think the solution described in one of the answers works for me and generates exactly the query that I want.

Ecto IN clauses with tuples

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})" …

[marciol]

Great library!