Best way to make dynamic query for sidebar filters?

I’m building a React front end that has a sidebar with checkboxes that filter down a base query. However, this is starting to get messy and i’m wondering what the best way to accomplish this is.

I’ve also noticed Ecto now has a dynamic function but i’m unsure how I can use this (if applicable at all).

Previously i’ve used reduce to compose the base query with additional where clauses but now I also need an and clause and am getting stuck. I’ve pasted in a cleaned up example below

This is the hard coded query I want to end up composing:

    base_query = from i in Interest,
      where: i.interest_list_id == ^interest_list_id,
      where: i.is_complete == true
      and (i.household_lives_in_foo == true or i.household_works_in_foo == true)

I’m unsure how to go about this with both my composed code below that’s using the field function to generate dynamic where clauses. What I want to do is:

and(acc, [r], [field(r, ^field1), field(r, ^field2)])

but that doesn’t work. Full example:

  # compose a more complex query based on data [{"age", %{eq: 21}}, {...}] 
  def fetch_completed_interests(interest_list_id, filter_params) do
    base_query = from i in Interest,
      where: i.interest_list_id == ^interest_list_id,
      where: i.is_complete == true
      # this is the query I need to make dynamic

    query_with_filters =
      Enum.reduce(filter_params, base_query, fn {field, selector}, acc ->
        case {field, selector} do

          {_field , %{eq: value}} ->
            where(acc, [r], field(r, ^field) == ^value)

          {_field , %{lt: value}} ->
            where(acc, [r], field(r, ^field) < ^value)

          {_field , %{or_group_true: value}} ->
            field1 = "household_lives_in_foo"
            field2 = "household_works_in_foo"
            # this is what I want to do but doesnt work (similar to where/3 above)
            # from:
            #      and(acc, [r], [field(r, ^field1), field(r, ^field2)])
            # to this:
            #     and (r.household_lives_in_foo == true or r.household_works_in_foo == true)

          {_field, %{}} -> # this can match *any* map not just an empty one

    {:ok, Repo.all(query_with_filters)}

I’m not sure about your specific example, but the Ecto 2.0 book covers dynamic queries in good detail, so it might be worth checking out:


Wow thanks for the link! I didn’t realize that was a thing. Will read it tonight :thumbsup:

You can take a look at a package I wrote. May be what you need, or may give you some ideas. ExQueb

Thanks @smpallen99 i’ll have to dig into that deeper when I rewrite the filter system.

1 Like