igor
How to add a group of or_where conditions to the query which already has some where closes
If I have a form with filters filtering entities by some field and I want to add a group of or_where closes to the query dynamically.
And the query could already have some where closes which I want my group of or_where to be separated from by AND operator in db query.
So, I’d like to build the function like:
def add_composite_where_to_query(query, field_name, values) do
# here is something I can't figure out..
end
This function should add where closes to query like:
SELECT * from entities e where (e.field1=‘value1’ OR e.field1=‘value2’) AND (e.field2=‘value3’ OR e.field2=‘value4’ OR …)
I’ve already tried many ways, but no luck.
As examples of what didn’t work:
def add_composite_where_to_query(query, field_name, values) do
Enum.reduce(values, query, fn(value, acc) ->
or_where(acc, [q], field(q, ^field_name) == ^value)
end)
end
def add_composite_where_to_query(query, field_name, values) do
dynamic = Enum.reduce(values, query, fn(value, dynamic) ->
or_where(dynamic, association, ^field_name == ^value)
end)
where(query, ^dynamic)
end
def add_composite_where_to_query(query, field_name, values) do
dynamics = Enum.reduce(values, [], fn(value, dynamics) ->
dynamic([q], field(q, ^field_name) == ^value)
end)
where(query, ^dynamics)
end
Most Liked
al2o3cr
FWIW, or_where does things that aren’t exactly intuitive - see the failing test here for an example. Be careful when composing queries with it.
OTOH, based on the function heads above I wonder if you’d be better served by an IN query rather than ORs since the query is checking for a single field being one of multiple values.
LostKobrakai
So where as well as or_where don’t just add a new condition to a query, but they also do it in a certain precedence order: (all_prev_conditions_of_the_query) AND|OR (newly_added_condition_s). So order plays an important role when composing where conditions.
If you’re looking for a different composition of conditions then you should probably look into the dynamic macro to build up the conditions and only attach the final result to the actual query.
LostKobrakai
If you need to group (put parenthesis around) conditions in a particular fashion, which contradicts the way precedence is handled by the where and or_where handling, then you need to create logic to explicitly create dynamic‘s for each group separately and only in the end put those groups together. Simply reducing over everything once probably won‘t cut it for those more complex conditions. As you cannot „add parenthesis“ after the fact you need to build up the correct groups when you‘re composing the single parts.
igor
Thanks for all of you, guys, especially for you, @LostKobrakai, it seems finally struck me, here is what I came to for now:
def build_query_from_filters(query, filters) do
if length(filters) do
first = hd(filters)
tail = tail(filters)
conditions = build_dynamic_from_filter(first)
conditions = Enum.reduce(tail, conditions, fn(filter, conditions) ->
filter_conditions = build_dynamic_from_filter(filter)
dynamic([q], ^filter_conditions and ^conditions)
end)
query |> where(^conditions)
else
query
end
end
def build_dynamic_from_filter(filter) do
if is_list(filter.value) do
conditions = false
Enum.reduce(filter.value, conditions, fn(value, conditions) ->
dynamic([q], field(q, ^filter.field_name) ilike ^value or ^conditions)
end)
else
dynamic([q], field(q, ^filter.field_name) ilike ^filter.value)
end
end
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Build Elixir applications with speed and confidence.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Enabling companies to succeed by building software people love.
Courses that'll move you from confusion to "Aha, now I get it!"