erlang (Ecto.Query.CompileError)元组只能用于与相同大小的文本元组进行比较,- Elixir

h7wcgrx3 于 2022-12-08 发布在 Erlang

关注(0)|答案(2)|浏览(161)

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?

erlang

来源：https://stackoverflow.com/questions/57401639/ecto-query-compileerror-tuples-can-only-be-used-in-comparisons-with-literal-tu

2条答案

按热度按时间

blpfk2vs1#

遗憾的是，应按照错误消息中的说明处理该错误：仅支持literal元组。
我无法想出更优雅、更不脆弱的解决方案，但我们总是把大锤作为最后的手段，其思想是生成并执行原始查询。

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

上面的语句应该在成功时返回{:ok, %Postgrex.Result{}}元组。

赞(0）回复(0）举报 2022-12-08

pgvzfuti2#

您可以为每个字段使用一个单独的数组，并使用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]

另一种方法是使用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]

更新：我现在看到了标签mysql，上面的是为postgres写的，但也许它可以作为一个mySql版本的基础。

赞(0）回复(0）举报 2022-12-08

我来回答

erlang (Ecto.Query.CompileError)元组只能用于与相同大小的文本元组进行比较,- Elixir

2条答案

相关问题

热门标签

最新问答