marcandre

marcandre

Ecto `fragment` with variable column

We have two tables with fields of type :map, which are copied from one to the other. I’d like a generic method that can do a query on one subfield, e.g. something like:

  defp with_hierarchical_data(model, field) do
    from(pa in model,
      select: [:id, ^field],
      where: fragment("#{^field}->>'selected_plan' IS NOT NULL")
    )
  end

I get "to prevent SQL injection attacks, fragment(…) does not allow strings to be interpolated as the first argument via the ^ operator, got: "#{^field}->>'selected_plan' IS NOT NULL"".

In my case, field is a trusted atom. How can I write this (other than writing two nearly identical functions) / bypass this prevention?

I looked for answers in a similar thread but didn’t see a solution

Marked As Solved

Also Liked

marcandre

marcandre

Thank you @cevado :heart:. Exactly what I was looking for.

Anyone having the same issue, what I was looking for was:

from(pa in model,
  select: ^[:id, field],
  where: fragment("?->>'selected_plan' IS NOT NULL", field(pa, ^field))
)
l00ker

l00ker

Try moving the interpolation outside of from.

defp with_hierarchical_data(model, field) do
  fd = "#{field}"

  from(pa in model,
    select: [:id, ^field],
    where: fragment("?->>'selected_plan' IS NOT NULL", ^fd)
  )
end

Edit: After thinking about this you may not even need to convert field to a string. Just use the atom and I believe it will convert it for you. The key is that you need to use ? and pass ^field as the 2nd argument to fragment()

defp with_hierarchical_data(model, field) do
  from(pa in model,
    select: [:id, ^field],
    where: fragment("?->>'selected_plan' IS NOT NULL", ^field)
  )
end

Where Next?

Popular in Questions Top

shahryarjb
Hello, I get Persian date from my client and convert it to normal calendar like this: def jalali_string_to_miladi_english_number(persi...
New
Tee
can someone please explain to me how Enum.reduce works with maps
New
albydarned
Hello all! I am typing this post from my new MacBook Pro with the M1 chip. I’m loving it so far, and will probably use it as my daily dr...
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
jononomo
I am trying to figure out how Mix knows whether the environment is test, dev, or prod -- where is this set? Thanks.
New
stefanchrobot
What’s the safe way to decode a JSON string into a struct? I want to avoid calling String.to_atom. Jason.decode can give me a map with st...
New
vac
Hi, I'm quite new in Elixir and I'm trying to format a string to a PEM format. I have the certificate value like MIIDBTCCAe2...... and ...
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
Codball
Mix format works fine if run from the cmd. I’ve followed this to facilitate the implementation into VSC which involves downloading an ext...
New
Qqwy
Original source of discussion: This topic on the Pragmatic Programmers' Functional Web Development with Elixir, OTP, and Phoenix forum. ...
New

Other popular topics Top

bsollish-terakeet
Credo is smart enough to check for (something like) this: assert length(the_list) == 0 with this response: Checking if an enum is empt...
New
myronmarston
The Elixir Typespec docs show the following syntax for keyword lists in typespecs: # ... | [key: type] # keyword lis...
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
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
johnnyicon
Hi all, I've just started learning Elixir and Phoenix Framework, so please pardon my n00bness at this stage. I'm trying to use Postg...
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
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
romenigld
I am trying to run a deploy with docker and I successfully runned with this command: docker build -t romenigld/blog-prod . but when I t...
New

We're in Beta

About us Mission Statement