avilesj

avilesj

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.

Showing Posts 1 to 3

LostKobrakai

LostKobrakai

t0t0

t0t0

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

andreh

andreh

Common, this is too good to be true! I’ve just removed 2 round-trips to the database and 15 lines of code thanks to you. You made my day!

— All posts loaded —

Where Next? Top

Trending in Questions Top

stjefim
Hello! Suppose you are building workflow (order / task / payment) processing system with the following requirements: Each workflow con...
New
Blokh
Hey guys, I’ve got a huge CSV ( around 10 GB ) that needs to be processed hourly Do you guys have any suggestions what is the best prac...
New
kszambelanczyk
Hello! Could someone please give me a help/sample code, how to delete a file from s3 using waffle/waffle_ecto from Phoenix app. I creat...
New
Onor.io
I have what I’ve heard referred to as a “lookup table” in my database. This is a way of assigning codes to common values. One common lo...
New
jaybe78
Hello, I’m developing a online persistent chat system (what’s app) like using elixir/dynamodb/aws for a mobile app(flutter). The diffic...
New
Trolleger
What approach to take when sending live updates to “random” users Hi! I have a question, I have a little chat app, and when I create a DM...
New
widianto
I think I’ve found a small improvement I could contribute to &lt;%= web_namespace %&gt;.CoreComponents (installer/templates/phx_web/compo...
New

Other Trending Topics Top

garrison
Hobbes is a low-level distributed database for the Elixir programming language. Hobbes provides a simple, safe, and scalable storage lay...
New
mcass19
ExRatatui lets you cook up rich terminal UIs in Elixir, powered by Rust’s ratatui via Rustler NIFs. Build interactive terminal applicatio...
New
Damirados
Hello everyone. After busy few months I am happy to announce v0.1.0 of Emerge &amp; Solve. They are GUI (Emerge) and State management (S...
New
netoum
Corex is an accessible, unstyled UI component library for Phoenix that integrates Zag.js state machines using Vanilla JavaScript and Live...
New
wintermeyer
There are three potential reasons for members of this forum to have a look at https://vutuv.de You are tired or annoyed of LinkedIn. Yo...
New
aseigo
ICal is a library for interacting with iCalendar data. It parses iCalendars into typed Elixir structs via ICal.from_ics, and can prepare ...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews