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

cevado

cevado

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

Last Post!

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))
)

Where Next?

Popular in Questions Top

rms.mrcs
Hi, I need to transform a list of numbers into a map where the keys are the indexes and the values are the original values of the list. ...
New
vertexbuffer
Hello, can anybody help here..? I have a list of players and I what to delete an element, but every for loop the list is reverting to ori...
New
New
hariharasudhan94
lets say i have a sample like a = 20; b = 10; if (a > b) do {:ok, "a"} end if (a < b) do {:ok, b} end if (a == b) do {:ok, "equa...
New
jay1
Why is it that the mnesia database isn’t the most preferred database for use in Elixir/Phoenix?
New
pmjoe
I have a relationship of love and hate with Elixir. Lots of things are just absolutely right, but there are some things that are kind of ...
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

Other popular topics Top

New
openscript
Hello! Sorry for this astonishing simple question, but I’m really stuck. I try to set up the intellij-elixir plugin, but I don’t know ho...
New
Darmani72
If I have a post route which an argument: post /my_post_route/:my_param1, MyController.my_post_handler How would get the post params ...
New
aadeshere1
I have a another noob question about loop. Since elixir is immutable, while loop is not directly possible. total = 10 while total != 0 ...
New
axelson
This post is a wiki (feel free to hit the edit button near the bottom right of this post to add your own changes!) This post collects co...
239 49134 226
New
jason.o
In the code below, if the create action is not set to accept “extra_key” as an input, it errors out with a message shown above. Is there ...
New

We're in Beta

About us Mission Statement