Making queries in ecto with filters based on a map

Questions / Help
1

I need to make a query using Ecto, using a map passed by params to filter the select. The thing is I dont want to build a ton of functions making pattern matching for every parameter.

I’ve done the following:

defp allowed_params do
    ~w(something, something2)s
end

def make_query(params) do
  filtered_params = Map.take(params, allowed_params)
  Enum.reduce(
      filtered_params,
      MyChangeset,
      fn {key, value} -> where([m], m[key] == ^value) end
    )
  |> Repo.all()
end

I’m receiving the following error:

`m[key]` is not a valid query expression. If you want to invoke Access.get/2 in a query, make sure that the module Access is required and that get/2 is a macro

It is crystal clear that m[key] is the wrong approach, but I can see a easy way to do it.
Is there any right way to do it?

1 Like
2

You should be able to use a combination of dynamic and field to achieve what you need.

There may be other ways? But most of my apps have tooling around this to achieve “segmentation” of a dynamic nature.

Would look like this:

import Ecto.Query

filtered_params = %{foo: "foo", bar: "bar"}

Enum.reduce(filtered_params, MySchema, fn {key, val}, queryable ->
  where(queryable, ^dynamic([m], field(m, ^key) == ^val))
end)
3 Likes
3

Damn, it worked. Looking back on my original code I feel kind of dumb.
I’ll run some tests on this, but thank you a lot. ^.^

4

The thing is I dont want to build a ton of functions making pattern matching for every parameter.

Just my two cents :slight_smile: , I think over time you’ll find it’s not so bad to be a bit more explicit even though it is more typing upfront. Two advantages of being more explicit / less magic:

  • Easier to understand
  • More flexible (what if you want to handle one param slightly differently?)

Here is how I did it in my app:

defmodule Planboard.Filter do
  alias Ecto.Queryable
  alias Ecto.Query

  @callback apply(queryable :: Queryable.t(), params :: map()) :: Query.t()
  @typep apply_key :: (queryable :: Queryable.t(), key :: atom(), value :: any() -> Query.t())

  @spec apply(Queryable.t(), map(), apply_key()) :: Query.t()
  def apply(queryable, params, apply_key) do
    Enum.reduce(params, queryable, fn {key, value}, query -> apply_key.(query, key, value) end)
  end
end

defmodule Planboard.Reservations.ReservationFilter do
  @behaviour Planboard.Filter
  alias Planboard.Reservations.ReservationQuery

  def apply(query \\ ReservationQuery.default(), params) do
    Planboard.Filter.apply(query, params, &apply_key/3)
  end

  defp apply_key(query, :during, period) do
    ReservationQuery.where_during(query, period)
  end

  defp apply_key(query, :types, []) do
    query
  end

  defp apply_key(query, :types, types) do
    ReservationQuery.where_types(query, types)
  end

  # ... (stuff ommited) ...

  defp apply_key(query, :start_date, %{lte: date}) do
    query |> ReservationQuery.where_start_date_lte(date)
  end

  defp apply_key(query, :end_date, %{gte: date}) do
    query |> ReservationQuery.where_end_date_gte(date)
  end

  defp apply_key(query, :sort_by, "start_date") do
    query |> ReservationQuery.order_by_start_date(:asc)
  end
end

I left out ReservationQuery but that’s just a module with functions that take an Ecto query and return an Ecto query.

This works also nicely in combination with Graphql / Absinthe, for example I can allow queries such as:

reservations(startDate: { lte: $date }, types: ["backoffice", "frontoffice"], sortBy: "start_date") {
   ...
}

Yes the approach you’re taking is fewer lines of code, but it breaks down if the mapping from param to database field isn’t 1:1 or when the filter value needs to be handled differently (where ids IN ... etc)

6 Likes
5

I totally agree with you

I faced this issue the minute I’ve implemented nsweeting suggestion (which was 100% correct for the problem that I presented). To provide a generic and global query builder and provide functions to proper generate the filters (I do not believe that they need to bee private) seems to be the most functional way to do it.

2 Likes