Combining ecto fragments


I am trying to build (let’s say) a bit more complex queries on the data blob (JSON object). Resulting queries are supposed to look like:

SELECT * FROM items WHERE data->>'price' ilike '%14%' OR data->>'title' ilike '%pi%';
Ideally with a large amount of AND and OR blocks

The thing is that in order to translate it into Ecto I have to use fragments:

Item |> where([i], fragment("data->>? ILIKE ?", ^key, ^value))

It looks like that the number of ? defines the number of arguments I have to pass into the fragment function. And I don’t understand if there is a way to make it more dynamic? E.g. if I want to combine a given number of fragments, how would I do it?

As far as I see if I will do:

Item |> where([i], fragment("data->>? ILIKE ?", ^key, ^value)) |> where([i], fragment("data->>? ILIKE ?", ^key1, ^value1))

I will get AND instead of OR in the resulting query.


I will get AND instead of OR in the resulting query.

Maybe or_where could help?

1 Like

You don’t if you’re on ecto >= 3.4.
You can do: from i in "items", where: ilike([^key], ^value) …

Compose the conditions using dynamic before attaching them to the query:

1 Like