How to write a "where in" clause on multiple fields

Questions / Help
, ,
1

Hi, I’m trying to write this query with Ecto but no luck neither with pure Ecto, nor with fragments.

The SQL is:

select * from tbl where (a, b) in (('val11', 'val12'), ('val21', 'val22'))

Here is what I tried (none of the following works):

values1 = [["val11", "val12"], ["val21", "val22"]]
values2 = [{"val11", "val12"},{"val21", "val22"}]

# Ecto funs
from t in Tbl, where: [t.a, t.b] in ^values1
from t in Tbl, where: [t.a, t.b] in ^values2

# fragments
from t in Tbl, where: fragment("(?, ?) in ?", t.a, t.b, ^values1)
from t in Tbl, where: fragment("(?, ?) in ?", t.a, t.b, ^values2)

# even this didn't work
from t in Tbl, where: fragment("(?, ?) in (('val11', 'val12'), ('val21', 'val22'))", t.a, t.b)

I’m now digging so deep into Postgrex.Protocol that I can’t get rid of the feeling I shouldn’t do that and I’m just missing something obvious.

2

You’re comparing against a list of tuples so you should specify a tuple here:

from t in Tbl, where: {t.a, t.b} in ^values2
3

wow, thanks, good and bad news:
good: I didn’t test it with static data and now when I did, it worked for

from t in Tbl, where: {t.a, t.b} in [{'val11', 'val12'}, {'val21', 'val22'}]

bad: I actually tested it with dynamic data, and it doesn’t work for

from t in Tbl, where: {t.a, t.b} in ^values2

throwing

** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size
4

Been a while since I last tried something similar and I don’t remember the details. Try Ecto’s dynamic function maybe?

5

I googled a bit on this error and discovered (i.e., Ecto IN clauses with tuples - #15 by halostatue) that dynamic tuples for “where in” is a known missing functionality in Ecto that is hard to implement. But there are a few hacky (compared to how it could’ve been written with plain Ecto) workarounds that I’m gonna try now :slight_smile: