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

Questions / Help
1

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?

2

@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:

3

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

2 Likes
4

This might be an interesting discussion:

1 Like
5

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

6

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

7

I need WHERE (f1, f2, fx...) > (v1, v2, vx...) instead of WHERE f1 > v1 AND f2 > v2 AND ...

Had exactly the same issue.

Late to the party, but I found a hack that lets me dynamically generate tuples from a given list of fields:

def field_tuples(fields, parent_alias) do
  {lhs, rhs} =
    Enum.map(fields, fn name ->
      {quote(do: field(x, unquote(name))),
        quote(do: field(parent_as(unquote(parent_alias)), unquote(name)))}
    end)
    |> Enum.unzip()

  quote do
    dynamic([x], {unquote_splicing(lhs)} > {unquote_splicing(rhs)})
  end
  |> Code.eval_quoted()
  |> elem(0)
end

# I use it as part of a recursive CTE
fields = [:group_id, :timestamp]

from p in Product,
  select: ^fields,
  where: ^field_tuples(fields, :cte),
  where: ^additional_criteria

# SQL output (shortened)
SELECT ssp0."group_id" AS "group_id", ssp0."timestamp" AS "timestamp"
FROM "product" AS ssp0
WHERE (ssp0."complete" = $1) AND ((ssp0."group_id", ssp0."timestamp") > (sc0."group_id",sc0."timestamp"))

Idea taken from here: Ecto not allowing string interpolation in fragments? - #22 by tfwright