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, > (?, ?) 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))

I’d like to have start_at(query, [:inserted_at, :id], ["2020-10-01 12:11:10", 1234]) generate the SQL fragment
WHERE (t.inserted_at, > ('2020-10-01 12:11:10', 1234)
for any values of fields and values.

I’ve even tried using fragment() but it doesn’t accept a list argument, like fragment("(?, ?)", fields).

Any ideas?

@garretsmith If fields and values are not required I recommend to use query data in one Keyword variable instead:

import Ecto.Query
# query = …
data = [inserted_at: ~N[2020-10-01 12:11:10], id: 1234]
Enum.reduce(data, query, fn {key, value}, query ->
  where(query, [table: t], field(t, ^key) > ^value)

If you want to have them in 2 variables then you need a bit more code:

import Ecto.Query
# query = …
start_at(query, [:inserted_at, :id], [~N[2020-10-01 12:11:10], 1234)

def start_at(query, [], []), do: query

def start_at(query, [key | keys], [value | values]) do
  |> where([table: t], field(t, ^key) > ^value)
  |> start_at(keys, values)

I also recommend this library:

I think I recall some library whose name eludes me that had a macro to do tuple comparison. It was ugly.

Personally, I’d go with @Eiji’s answer (Reducing a list), though I think his algorithm is incorrect.

In SQL (a, b) > (U, V) is equivalent to a > U OR ((a = U) AND (b > V)) which is pretty gnarly but with a smart use of recursion such a query can be generated starting from a keyword list.

EDIT: found the library I mentioned that does exactly that:

1 Like

This might be an interesting discussion:

1 Like

Thank you Eiji. I don’t need fields and values in separate lists, a single tuple list is fine, but I do need the keys & values formatted differently in the SQL, specifically I need WHERE (f1, f2, fx...) > (v1, v2, vx...) instead of WHERE f1 > v1 AND f2 > v2 AND ....

ecto_shorts looks very interesting though, TY

Thank you 1player, that approach is the most promising one I’ve found so far. paginators use of dynamic() is very instructive.