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

Question

Where I'm at

For this example, consider Friends.repo

Table Person has fields :id, :name, :age

Example Ecto query:

iex> from(x in Friends.Person, where: {x.id, x.age} in [{1,10}, {2, 20}, {1, 30}], select: [:name])

When I run this, I get relevant results. Something like:

[
  %{name: "abc"},
  %{name: "xyz"}
]

But when I try to interpolate the query it throws the error

iex> list = [{1,10}, {2, 20}, {1, 30}]
iex> from(x in Friends.Person, where: {x.id, x.age} in ^list, select: [:name])
** (Ecto.Query.CompileError) Tuples can only be used in comparisons with literal tuples of the same size

I'm assuming I need to do some sort of type casting on the list variable. It is mentioned in the docs here : "When interpolating values, you may want to explicitly tell Ecto what is the expected type of the value being interpolated"

What I need

How do I achieve this for a complex type like this? How do I type cast for a "list of tuples, each of size 2"? Something like [{:integer, :integer}] doesn't seem to work.

If not the above, any alternatives for running a WHERE (col1, col2) in ((val1, val2), (val3, val4), ...) type of query using Ecto Query?

score 1 · Answer 1 · answered Aug 08 '19 at 07:38

1

Unfortunately, the error should be treated as it is stated in the error message: only literal tuples are supported.

I was unable to come up with some more elegant and less fragile solution, but we always have a sledgehammer as the last resort. The idea would be to generate and execute the raw query.

list = [{1,10}, {2, 20}, {1, 30}]
#⇒ [{1, 10}, {2, 20}, {1, 30}]
values =
  Enum.join(for({id, age} <- list, do: "(#{id}, #{age})"), ", ")
#⇒ "(1, 10), (2, 20), (1, 30)"


Repo.query(~s"""
  SELECT name FROM persons
  JOIN (VALUES #{values}) AS j(v_id, v_age)
  ON id = v_id AND age = v_age
""")

The above should return the {:ok, %Postgrex.Result{}} tuple on success.

answered Aug 08 '19 at 07:38

Aleksei Matiushkin

119,336
10
100
160

Yes I ended up using a raw query as well. Although I still stuck to using WHERE IN clause – Kaushik Evani Aug 08 '19 at 08:41
Eh. The SQL above is (should be) more performant than `WHERE IN` clause; if you insist on using `WHERE IN`, it’s still easily achievable by slight updating the code that generates a string. You might better file an issue to Ecto asking about the plan to support dynamically generated tuples in `in` clause. – Aleksei Matiushkin Aug 08 '19 at 08:48
We are using MySQL. Looks like using `VALUES` outside of `INSERT` is not supported in MySQL. It is annoying really. All other SQL DBs have this feature. By the way, why is this better than `WHERE IN`? – Kaushik Evani Aug 08 '19 at 12:08
1

Weird enough, now I cannot find the reference; I was so many times told by our DBAs it’s _better_, I was just blindly trusting them. – Aleksei Matiushkin Aug 08 '19 at 12:53

AlexDev · Answer 2 · 2020-07-07T19:43:26.893

You can do it with a separate array for each field and unnest, which zips the arrays into rows with a column for each array:

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]

another way of doing it is using json:

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]

Update: I now saw the tag mysql, the above was written for postgres, but maybe it can be used as a base for a mySql version.

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

2 Answers2