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.

Where Next?

Popular in Questions Top

chokchit
** (DBConnection.ConnectionError) connection not available and request was dropped from queue after 2733ms. You can configure how long re...
New
_russellb
I want to try my hand at web scraping. What tools/libraries do I need to use. I’m hoping to turn this into something professional so don’...
New
New
Harrisonl
We have an ECS cluster with 4 services, where each task joins a single cluster, via discovery ECS discovery service. Currently when I de...
New
electic
Hi, I am new to Elixir. I am trying to use the DateTime component to insert a date into MySQL however the there seems to be no way to fo...
New
ovidiubadita
Hey all, I discovered Elixir and I love it. I always wanted to learn a functional programming and I intended to go for Haskell, but afte...
New
LegitStack
I’m trying to make a websocket server in Phoenix or raw Elixir. I heard about gun, I think I could use cowboy, but since I’m not that sma...
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
SoCreat
i’m a new one to elixir which editor can i use vs code? or atom? Thanks! :smiley:
New
nobody
Hi! In PHP: $_SERVER[‘SERVER_ADDR’] - in Elixir? Searched the docs for ip address and the web, no good results. Thanks!
New

Other popular topics Top

marius95
Hello everyone, I try to use an Javascript Event Handler in my root.html.leex file. Therefore I created a function in the app.js file: ...
New
mcarvalho
What is the difference between System.get_env and Application.get_env? For example, what are best practices to use one versus another.
New
skosch
To my knowledge, put_in, Map.update etc. all have the one limitation of not automatically creating intermediate keys when needed (for exa...
New
Patoshizzle
After calling mix ecto.create I get this error: 17:00:32.162 [error] GenServer #PID<0.412.0> terminating ** (Postgrex.Error) FATAL...
New
shahryarjb
Hello, I have map which I want to convert it to string like this: the map: %{last_name: "tavakkoli", name: "shahryar"} the string I ne...
New
saif
Hello everyone, Long time lurker first time poster here. I’ve recently begun working on Elixir full-time again! :raised_hands: It’s been...
New
shijith.k
I am trying to start a new phoenix project with elixir 1.9, but mix phx.new does not work. It says that ** (Mix) The task "phx.new" could...
New
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 records...
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
jononomo
For some reason my phoenix channels are working for me in my local dev environment, but as soon as I deploy via Docker, I get a 403 error...
New

We're in Beta

About us Mission Statement