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

[garretsmith]

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 SQL fragment
WHERE (t.inserted_at, t.id) > ('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?

[Eiji]

@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)
end)

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
  query
  |> where([table: t], field(t, ^key) > ^value)
  |> start_at(keys, values)
end

I also recommend this library:

ecto_shorts

[1player]

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: https://github.com/duffelhq/paginator/blob/master/lib/paginator/ecto/query.ex#L50-L88

[LostKobrakai]

This might be an interesting discussion:

Ecto IN clauses with tuples

I wanted to highlight how I’m using these (the code is going to production tomorrow). Essentially, I’m getting my data records like this: activity = PendingDataExchange |> where(exchange_type: ^config.exchange) |> where([pde], pde.updated_at <= ^config.timestamp) |> select([pde], map(pde, ~w(user_id business_id exchange_type update_type)a)) |> limit(^config.size) |> Repo.all(timeout: config.timeout) Later, I’m loading records based on the activity in question: records = load_rec…

[garretsmith]

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

[garretsmith]

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