Ecto IN clauses with tuples

Questions / Help
,
16

Here is an example of both methods, the array per field and the jsonb_to_recordset, using ecto.

Array per field + unnest:

ids =[ 1,  2,  1]
ages=[10, 20, 30]

from x in Friends.Person, 
inner_join: j in fragment("SELECT distinct * from unnest(?::int[],?::int[]) AS j(id,age)", ^ids, ^ages),
        on: x.id==j.id and x.age==j.age,
select: [:name]

jsonb_to_recordset:

list = [%{id: 1, age: 10}, 
        %{id: 2, age: 20}, 
        %{id: 1, age: 30}]

from x in Friends.Person,
inner_join: j in fragment("SELECT distinct * from jsonb_to_recordset(?) AS j(id int,age int)", ^list),
        on: x.id==j.id and x.age==j.age,
select: [:name]
6 Likes