boriscy

boriscy

Pass a list or other type to custom Ecto query

I have a query like this one

select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
where t->>'text' in($1)

Is there any way I can pass a list (Array) to the query, I get an error if I pass a list this way Ecto.Adapters.SQL.query(MyRepo, sql, ["tag1"])

Most Liked

belaustegui

belaustegui

You should use a query fragment to wrap the JSON access:

# Assuming that the variable input = ["tag1"]
select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
 and fragment("(?->>?)", t, "text") in ^input
boriscy

boriscy

As you can see in the example the fragment is used in the where clause so to make things work I should create a view.

boriscy

boriscy

It doesn’t work if I use in ($1) but If I change the query to

select o.id::text, o.name, o.geom, o.tags, o.address, o.open
from organizations o, jsonb_array_elements(tags) as t
where t->>'text' = any($1)

will work I would like to know if there is a way to make work in the future and would be really nice to have instead of numbers named params like ..where t->>'text' = any($tags)", [tags: ["tag1"]], thanks for the help.

josevalim

josevalim

Creator of Elixir

Using any is a good call. You should be able to make it work with fragments too by doing fragment("t->>'text' = any(?)", ^list).

The query API is a direct proxy to the database, so there are no plans to support something as $tags unless the database also supports it.

Where Next?

Popular in Questions Top

dotdotdotPaul
Okay, I'm having a heck of a time trying to figure out how to best handle the validation of belongs_to associations in Ecto. I'm sure I'...
New
lessless
I believe there are people here who are dealing with CSV files import on the daily basis, and since Excel is a really popular tool there ...
New
LegitStack
I’m hoping you guys can give me some general advice and perhaps code examples if you’re feeling up to it. I’m very interested in Elixir,...
New
JorisKok
I have a server on AWS, and was running a load test using artillery. When looking at the Phoenix dashboard I see the Ports going to 100% ...
New
aalberti333
As the title describes, I’m trying to run Enum.map() over a list of key/value pairs, where the value is a map. My data looks like this: ...
New
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
chrisalley
ExUnit now has describe blocks which is a welcome addition coming from RSpec. In the docs, it states that nested hierarchies of describe ...
New
Fl4m3Ph03n1x
About me? ( if you have nothing better to do than reading about some random guy in the internet :stuck_out_tongue: ) Hello all, this is ...
New
Qqwy
Original source of discussion: This topic on the Pragmatic Programmers' Functional Web Development with Elixir, OTP, and Phoenix forum. ...
New
siddhant3030
Hi, I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw ...
New

Other popular topics Top

joaquinalcerro
Hi there, I am working with Ecto-Postgresql and I need to call all of the records from a specific table but the table has 40,000 record...
New
jerry
Good day to you all. I have been struggling to get a query involving like and ilike to work. Can anyone assist me on this, please? pro...
New
sergio_101
I am VERY much an elixir newbie. I have taken one elixir course and one phoenix course on Udemy. During that course, I saw the instructor...
New
ycv005
I have followed this StackOverflow post to install the specific version of Erlang. And When I am running mix ecto.setup then getting fol...
New
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
lastday4you
I wanted to check elixir version in phoenix because i found that my elixir is 1.5 but when i use Enum.chunk_by it said the function is un...
New
aesmail
Hello guys, I have finally made it. I created an admin interface for a framework. It’s been on my todo list for years and with the curre...
New
vrod
I am using the Starship cross-shell prompt – it seems pretty nice, but I get some errors: [WARN] - (starship::utils): Executing command ...
New
lanycrost
Hi everyone! I need implement if…else if…else condition from my elixir code, and anymore of this control flow structures not work proper...
New
joeerl
Hello again - after a longish gap I’ve decided I really must dig into Elixir and see what’s been happening here - so I have a few questio...
New

We're in Beta

About us Mission Statement