Querying an array of ints in PostgreSQL using Ecto

Questions / Help
, ,
1

Hello community.

I am trying to perform the following query in Ecto

SELECT *
FROM table t 
where ARRAY[2, 12] <@ list_of_ids ;

The closest I have gotten to is

from t in Table,
where: fragment("ARRAY[?] <@ ?", ^list_of_ids, r.list_of_ids)

Performing this query returns the error

(Postgrex.Error) ERROR 42883 (undefined_function) operator does no
t exist: text[] <@ integer[]

Now, I am confident the value of ^list_of_ids is a list of integers. Even so, if I hard code the values in the fragment, it works.

from t in Table,
where: fragment("ARRAY[?] <@ ?", [2,12], r.list_of_ids)

Last night I was searching about similar queries and the only similar issue I was able to find was this one in SO..

I made it work through raw query → Repo.load but I would like to know the real syntax for this operation.

So, my question is:

What is the proper Ecto syntax to accomplish what I am trying to do?

Thank you all.

2

https://hexdocs.pm/ecto/3.12.5/Ecto.Query.API.html#splice/1

This should help.

2 Likes
3

Hi,

you shouldn’t have to wrap your values between ARRAY[ … ], could you try:

  • fragment("? <@ ?", ^list_of_ids, r.list_of_ids)
  • fragment("?::INT[] <@ ?", ^list_of_ids, r.list_of_ids)
  • fragment("? <@ ?", type(^list_of_ids, {:array, :integer}), r.list_of_ids)
  • fragment("? <@ ?", type(^list_of_ids, r.list_of_ids), r.list_of_ids)

Nevermind, I believed that Ecto was smart enough to handle it directly (without splice/1) and the problem was related to typing (this is quite frequent with PostgreSQL).

2 Likes