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

vonH
When I run the Plug and I recompile I wind up having to use Ctrl C to quit iex and start again. Witht the help of rlwrap I can use the cu...
New
PeterCarter
There are pre-rolled solutions for other frameworks that do work. However, Phoenix does not seem to have these. Have people had good expe...
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
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
fireproofsocks
Forgive me if this is obvious, but how does one delete a database record WITHOUT selecting it first? Ecto.Repo — Ecto v3.14.0 has exampl...
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
vrod
I am using the Starship cross-shell prompt – it seems pretty nice, but I get some errors: [WARN] - (starship::utils): Executing command ...
New

Other popular topics 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
hariharasudhan94
Lets say I have map like this fetching from my database %{"_id" => #BSON.ObjectId<58eb1a7a9ad169198c3dXXXX>, "email" => ...
New
JakeBecker
TL;DR: I’ve just released an implementation of Microsoft’s IDE-independent Language Server Protocol for Elixir. It adds language support ...
1144 54996 245
New
sen
Hi All, I set a environment variables in dev.exs , like below code. when i start server, how can i set the ${enable} value? thanks. d...
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
AstonJ
Posting this to see if we can make things easier for people to get into Neovim. If you use Neovim and have a favourite distro please let ...
New

We're in Beta

About us Mission Statement