Building dynamic queries with Ecto

Let’s create a new Elixir module that will implement logic of our future dynamic query. I will name it FilterQuery :

  defmodule FilterQuery do
    @moduledoc """
    Query that accepts inclusion or exclusion parameters and filters by this parameters
    """

    import Ecto.Query
  end

This module should have just one public function called filter that accepts incoming query, inclusion and exclusion parameters as the arguments:

  @spec filter(Ecto.Query.t, map, map) :: Ecto.Query.t
  def filter(query, inclusion, exclusion) 

Now let’s implement the core of our future filter function. Assume that we need to build two lists of dynamic queries both for inclusion and exclusion parameters:

  queries = dynamic_query(:inclusion, inclusion) ++ dynamic_query(:exclusion, exclusion)

Now we need to merge all these dynamic queries into a new big one. It can be implemented using Enum.reduce :

  Enum.reduce(queries, query, fn(q, acc) -> where(acc, ^q) end)

Now our filter function returns a new complex query that can be easily composed with another queries using Ecto.Query functions or macroses.

We have just created a basic prototype for our future dynamic filter query. However, the devil’s always in the details. We need to hide implementation in private functions:

  defp dynamic_query(type, filters) when type in [:inclusion, :exclusion] do
    for {attr, values} <- filters, present?(values),
      do: dynamic_query(type, attr, values)
  end

  defp dynamic_query(:exclusion, exclusion) do
    for {attr, values} <- exclusion, do: dynamic_query(:exclusion, attr, values)
  end

We suppose that inclusion and exclusion filters are keyword lists with keys as model attributes and lists as the filter values. Therefore, we need to check if these lists contain values or we shall skip building query:

  defp present?(list) when is_list(list), do: length(list) > 0
  defp present?(_), do: false

Now we need to go straight to the implementation. dynamic_query/3 should finally return for us the result.

We will use dynamic macro from Ecto.Query that takes any query as first argument and builds dynamic query for the second one. Also notice that we need to use field macro to dynamically access attribute name:

  defp dynamic_query(:inclusion, attr, values) do
    dynamic([q], field(q, ^attr) in ^values)
  end

  defp dynamic_query(_, attr, values) do
    dynamic([q], field(q, ^attr) not in ^values)
  end

So, looks like our filter query is fully functional and dynamic! But this is not enough when you are building a complex logic that desperately needs to leave some attributes unfiltered.

For example, imagine the case when your User model has a relationships_status attribute. If you’re allowing your API customers to filter any attributes they are likely to find out lonely users by using this loophole. Therefore your customers will be dating, engaging, tying the knots and finally will be lost for your application. Is this what you really wished? Guess not :)

So let’s restrict filtered attributes by using __using__ macro:

  defmacro __using__(attributes: allowed_attributes) do
  ...

    def filter(query, inclusion, exclusion) do
      [inclusion, exclusion] = [sanitize(inclusion), sanitize(exclusion)]

      queries = dynamic_query(:inclusion, inclusion) ++ dynamic_query(:exclusion, exclusion)
      Enum.reduce(queries, query, fn(q, acc) -> where(acc, ^q) end)
    end
  ...

     defp sanitize(keyword_list),
      do: for {key, val} <- keyword_list, allowed_key?(key), do: {key, val}

     defp allowed_key?(key), do: key in unquote(allowed_attributes)
  ...
  end

Voila! No one ever will find out how your users relationships are going.

Now let’s take a quick look at our dynamic filter query:

  defmodule FilterQuery do
    @moduledoc """
    Query that accepts inclusion or exclusion parameters and filters by this parameters
    """

    import Ecto.Query

    defmacro __using__(attributes: allowed_attributes) do
      quote do
        import FilterQuery

        @spec filter(Ecto.Query.t, map, map) :: Ecto.Query.t
        def filter(query, inclusion, exclusion) do
          [inclusion, exclusion] = [sanitize(inclusion), sanitize(exclusion)]

          queries = dynamic_query(:inclusion, inclusion) ++ dynamic_query(:exclusion, exclusion)
          Enum.reduce(queries, query, fn(q, acc) -> where(acc, ^q) end)
        end

        def filter(query, _) do
          query
        end

        defp dynamic_query(type, filters) when type in [:inclusion, :exclusion] do
          for {attr, values} <- filters, present?(values),
            do: dynamic_query(type, attr, values)
        end

        defp dynamic_query(:exclusion, exclusion) do
          for {attr, values} <- exclusion, do: dynamic_query(:exclusion, attr, values)
        end

        defp dynamic_query(:inclusion, attr, values) do
          dynamic([q], field(q, ^attr) in ^values)
        end

        defp dynamic_query(_, attr, values) do
          dynamic([q], field(q, ^attr) not in ^values)
        end

        defp sanitize(keyword_list),
          do: for {key, val} <- keyword_list, allowed_key?(key), do: {key, val}

        defp allowed_key?(key), do: key in unquote(allowed_attributes)

        defp present?(list) when is_list(list), do: length(list) > 0
        defp present?(_), do: false
      end
    end
  end

It’s a piece of cake to use our query in another modules. Just include our query and set the list of allowed attributes through use construction:

  {:ok, _} = Application.ensure_all_started(:ex_machina)

  defmodule Example do
    @moduledoc """
    Example of using dynamic ecto queries
    """

    import Factory
    use FilterQuery, attributes: ~w(proficiency name)a

    def run do
      insert_users()

      IO.inspect(count([])) # 3
      [relationships_status: ["married"]] |> count([]) |> IO.inspect # 3
      [proficiency: ["developer"]] |> count([]) |> IO.inspect # 1
      [proficiency: ["soldier"]] |> count([]) |> IO.inspect # 0
      IO.inspect(count([], %{proficiency: ["politic"]})) # 2
    end

    defp insert_users do
      Repo.delete_all(User)

      insert(:user, relationships_status: "single", proficiency: "developer")
      insert(:user, relationships_status: "married", proficiency: "politic")
      insert(:user, relationships_status: "dating", proficiency: "thief")
    end

    defp count(inclusion, exclusion \\ []) do
      User
      |> filter(inclusion, exclusion)
      |> Repo.aggregate(:count, :id)
    end
  end

However, this is not at all that we can achieve by using dynamic macro in Ecto.

You can filter by regex, type or even your own query; sort by ascending and descending; do aggregation or pagination; whatever comes to your head - it all depends on your imagination and skills. Ecto provides you a great tool to build any complex queries by writing minimal lines of code without any duplication.

What’s next? See the full example for this article or read Ecto documentation to deepen your knowledge a little bit more.

Happy hacking!