How to add a group of or_where conditions to the query which already has some where closes

Questions / Help
, ,
1

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
2

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.

2 Likes
3

Thanks for the help!
I’m already aware of such or_where behaviour because my first attempt:

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

produced exactly same results. It doesn’t wrap the OR’s with parentheses.

As for the IN -yes, it could help in case of '==' operator but if I wanted to use ILIKE operator for every option I will need OR anyway.

4

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.

1 Like
5

Ok, I tried to do what you suggested but it seems I can’t do it like this:

def build_query_from_filters(query, filters) do
  conditions = false
  conditions = Enum.reduce(filters, conditions, fn(filter, conditions) ->
    # here we need to build dynamic
    conditions = dynamic([q], build_filter_conditions(filter) and ^conditions)
  end)
  query |> where(^conditions)
end

def build_filter_conditions(filter) do
  if is_list(filter.value) do
    # here we need to return something
  else
    # and here too..
  end
end

Can I extract building conditions for one filter into a separate function taking into account that dynamic is a macro, what should it return then?

6

You should invert it rather, have build_filter_conditions take both a filter and the query, like this:

def build_query_from_filters(query, filters) do
  Enum.reduce(filters, query, build_filter_conditions/2)
end

def build_filter_conditions(filter, query) do
  if is_list(filter.value) do
    where(query, whatever_conditions)
  else
    where(query, whatever_other_conditions)
  end
end
7

Yes, this is what I initially did but it didn’t allow me to build the query of type:
SELECT * FROM entity e WHERE e.some_field = 'some_val' AND ... AND (e.filter1_field_name ILIKE 'value_1' OR e.filter1_field_name ILIKE 'value_2' OR ... e.filter1_field_name ILIKE 'value_n') AND ... AND (e.filter_m_field_name ILIKE 'value_m_1' OR ... OR e.filter_m_field_name ILIKE 'value_m_k')

Notice, please that we need to wrap query’s OR closes with parentheses for filter to work correctly.

As already been mensioned, if we had == equality operator instead of ILIKE operator for closes joined by OR we could just use the IN operator like:
if is_list(filter.value),do: where(query, [q], field(q, ^filter.field_name) IN filter.value)
But in case of ILIKE we can’t.

8

Found two similar topics on this forum:

That’s where the dynamics were presented by Ecto. But I can’t figure out how to apply them to my case.

9

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.

1 Like
10

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
1 Like