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

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
nobody
Hi! In PHP: $_SERVER[‘SERVER_ADDR’] - in Elixir? Searched the docs for ip address and the web, no good results. Thanks!
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
nsuchy
Hi. I’ve noticed that Windows Powershell has it’s own IEX command and you cannot access Elixir’s IEX due to the conflict. This isn’t a cr...
New
greenz1
I have a phoenix application from which a user can download multiple(5-6) files of size 1MB. I couldn’t find anything related to sending ...
New
vrod
I am using the Starship cross-shell prompt – it seems pretty nice, but I get some errors: [WARN] - (starship::utils): Executing command ...
New
WestKeys
Currently suffering from paralysis by [HTTP client] analysis. This is rather unusual in Elixirland as there tends to be consensus on the ...
New

Other popular topics Top

baxterw3b
Hi guys, i’m new in the Elixir world, and i have to say, that i love it! i’m having some problem to understand anonymous functions with ...
New
ashish173
I am using Ecto timestamps with postgres, I can see the timestamps() use the :naive_dateime but for my use case I wanted to store the ti...
New
AstonJ
Seen any cool LiveView demos, sample apps or examples? Please post them here! :003:
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
siddhant3030
Hi, I have to write a raw query for one of my project. But till now I have used ecto queries and don’t have much experience writing raw ...
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

We're in Beta

About us Mission Statement