CODEWITHSUNDEEP
elixirecto
john
john

Reputation: 1075

Parameterize value after @> operator using fragments

I am trying to essentially run this query in ecto but with a parameterized value:

select * from accounts.businesses where admins @> '{1234}';

This is what I have as my function definition:

def get_businesses_by_id(id) do
  from(b in Businesses, where: fragment("? @> '{?}'", b.admins, ^id))
  |> Repo.all()
end

but I get the following error:

** (ArgumentError) parameters must be of length 0 for query %Postgrex.Query

If I hardcode the value directly in then it works:

def get_businesses_by_id(id) do
  from(b in Businesses, where: fragment("? @> '{1234}'", b.admins))
  |> Repo.all()
end

Any insight into how I can effectively parameterize the ID value?

Upvotes: 0

Views: 265

Answers (2)

Hauleth
Hauleth

Reputation: 23566

You cannot use "placeholder" inside string, but you do not need string, you need array, so you can do:

fragment("? @> ?", b.admins, ^[id])

But as GIN index also supports = operator on arrays it should also work with ANY so you could just write:

where: ^id in b.admins

Upvotes: 3

oldhomemovie
oldhomemovie

Reputation: 15129

Unless using @> operator is absolutely necessary, using ANY should work:

def get_businesses_by_id(id) do
  from(b in Businesses, where: fragment("? = ANY(?)", ^id, b.admins))
  |> Repo.all()
end

Upvotes: 0

Related Questions