Translating SQL query with row constructors (?) into Ecto

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