Search code examples
elixirecto

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?

Solution

  • 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