Search Filters with Ecto Queries

We have also written an accompanying Livebook for this article that includes the final code for you to play around with.

Use case: articles and authors

Let's say you're building an admin interface for blog articles, and you want to offer a function to filter the articles based on multiple fields like title, summary, or status as well as fields of the associated author. The web form for the filters might look something like this:

If a user fills out one filter form field, only the matching records should be returned. If a form field is left blank, it should be skipped when building the query. So leaving all fields empty should lead to a response that includes all records.

The corresponding Ecto schemas for article and author are defined in the usual way:

defmodule Author do  
  use Ecto.Schema​  

  schema "authors" do    
    field :first_name, :string    
    field :last_name, :string  
   end
end​

defmodule Article do  
  use Ecto.Schema​  

  schema "articles" do    
    field :title, :string    
    field :summary, :string    
    field :status, Ecto.Enum, values: [:draft, :published, :deleted]    
    belongs_to :author, Author​    

    timestamps()  
  end
end

In this blog article, we focus on the code to build the correct Ecto Query based on the filter values and omit the code for transforming the raw form data into a struct. If you want to see the complete picture, the accompanying Livebook includes a way to use an embedded_schema for handling the input values. For now, let's assume the filter values are represented by this map:

filter = %{  
  title_eq: "Implementing Search Filters with Ecto Queries",
  summary_eq: nil,
  status_in: [:published, :draft],
  author_first_name_eq: nil,  
  author_last_name_eq: nil
}

Why don't we just build a normal query?

​The default way of applying this filter to an  Ecto.Query would look like this:

def apply_filter(query, filter) do
  query
  |> where([article], article.title == ^filter.title_eq)
  |> where([article], article.summary == ^filter.summary_eq)
  |> join(:left, [article], author in assoc(article, :author), as: :author)
  |> where([article, author: author], author.first_name == ^filter.author_first_name_eq)
  |> where([article, author: author], author.last_name == ^filter.author_last_name_eq)
end 

While this approach is easily readable, it does not meet the requirement that empty form values should be skipped when building the query. Additionally, if neither author_first_name_eq nor author_last_name_eq is filled, then the join should be skipped as well to make the query cheaper.

Basic filters

Let's start with building a filter for one field. We build one implementation that simply returns the query untouched if the form field was submitted empty, and a second implementation that adds the where condition.

def filter_title_eq(query, %{title_eq: nil}), do: query

def filter_title_eq(query, %{title_eq: title_eq}) do
  where(query, [article], article.title == ^title_eq)
end

However, if we want to use this approach for multiple fields, it could become cumbersome to repeat the approach multiple times. Luckily, Ecto provides field/2 macro that enables us to build a function that takes the column name as an argument, as shown for an eq and an in filter:

def filter(query, _field_name, _operator, nil), do: query

def filter(query, field_name, :eq, value) do
  where(query, [record], field(record, ^field_name) == ^value)
end

def filter(query, field_name, :in, values) do
  where(query, [record], field(record, ^field_name) in ^values)
end

Following this approach, now we can chain the filters:

def apply_filter(query, filter) do
  query
  |> filter(:title, :eq, filter.title_eq)
  |> filter(:summary, :eq, filter.summary_eq)
  |> filter(:status, :in, filter.status_in)
end

Filters on associated records

In order to extend the approach to fields on associated records, let's start again with a less dynamic version to show what we want to achieve:

def filter_author_first_name_eq(query, %{author_first_name_eq: nil}), do: query

def filter_author_first_name_eq(query, %{author_first_name_eq: author_first_name_eq}) do
  query
  |> left_join_author_once()
  |> where([article, author: author], author.first_name == ^filter.author_first_name_eq)
end

def left_join_author_once(query) do
  if has_named_binding?(query, :author) do
    query
  else
    join(:left, [article], author in assoc(article, :author), as: :author)
  end
end

This approach already addresses the issue that the author association should only be joined when necessary, and also prevents joining the association twice by checking if the association has already been made via has_named_binding?/2. In order to make this more dynamic and reusable, we have to turn the :author atom into a parameter:

def assoc_filter(query, _assoc, _field, _operator, nil), do: query

def assoc_filter(query, assoc, field, :eq, value) do
  query
  |> left_join_once(assoc)
  |> where([record, {^assoc, s}], field(s, ^field) == ^value)
end

def left_join_once(query, assoc) do
  if has_named_binding?(query, assoc) do
    query
  else
    join(query, :left, [record], s in assoc(record, ^assoc), as: ^assoc)
  end
end

We can now combine all filter values:

def apply_filter(query, filter) do
  query
  |> filter(:title, :eq, filter.title_eq)
  |> filter(:summary, :eq, filter.summary_eq)
  |> filter(:status, :in, filter.status_in)
  |> assoc_filter(:author, :first_name, :eq, filter.author_first_name_eq)
  |> assoc_filter(:author, :last_name, :eq, filter.author_last_name_eq)
end

Adding custom filters

While we can support plenty of filters in a dynamic way as described above, there will always be some custom cases that require custom search conditions. Let's say you want to add a "Most Relevant" checkbox to your search form that limits the results to articles that have been published and are newer than one month. In order to support this, we add a most_relevant key to the filter struct. You can now write a function like this (note that you still have to care about omitting the condition if the box is not checked):

defp filter_most_relevant(query, true) do
  query
  |> where([article], article.status == :published)
  |> where([article], article.inserted_at > ago(1, "month"))
end

defp filter_most_relevant(query, _), do: query

Conclusion

​We have shown how to transform the user input of a search form into the corresponding Ecto Query by using some lesser known Ecto features like field/2 and has_named_binding?/2. We can build helper functions that allow building most filter queries in a nicely readable and chainable way, while still allowing for extending the queries with custom functions.