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))
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).
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 ....