Search Filters with Ecto Queries: Accompanying Livebook
Mix.install([
{:ecto, "~> 3.8"}
])
The generic filter module
This livebook is meant to be a companion playground for this article on the Zweitag Blog. In order to play with the code, you need a running livebook instance, either locally or in the cloud via the “Run in Livebook” badge.
Within the blog article, we describe the reasoning for building functions to aid in filtering. In order to make them reusable, we have put them into their own module. This is meant to be a place to also add more generic filter functions.
defmodule EctoFilter.Query do
import Ecto.Query
# 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
# assoc_filter
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
defp 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
end
The application code
We assume that you already have existing schemas within your application. For our example case we define them as follows:
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
As described in the blog article, we want to back a web form to handle the search form input data. In order to do this, we use an embedded_schema
for our filter.
defmodule ArticleFilter do
use Ecto.Schema
import Ecto.Changeset
import Ecto.Query
import EctoFilter.Query
embedded_schema do
field(:title_eq, :string)
field(:summary_eq, :string)
field(:status_in, {:array, :string})
field(:author_first_name_eq, :string)
field(:author_last_name_eq, :string)
field(:most_relevant, :boolean)
end
def build_filter(attrs) do
cast(%__MODULE__{}, attrs, [
:title_eq,
:summary_eq,
:status_in,
:author_first_name_eq,
:author_last_name_eq,
:most_relevant
])
|> apply_action(:build)
end
def apply(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)
|> filter_most_relevant(filter.most_relevant)
end
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
end
The following code shows how the filter can be used within your application code. Using the embedded_schema
as described above also allows us to handle casting errors for our inputs; and we would also be able to add custom validations to our filter inputs.
# Building a filter from valid input
{:ok, filter} =
ArticleFilter.build_filter(%{
"title_eq" => "hello",
"summary_eq" => "foo",
"status_in" => ["draft", "published"],
"author_first_name_eq" => "first_name",
"author_last_name_eq" => "last_name",
"most_relevant" => "1"
})
query = ArticleFilter.apply(Article, filter)
dbg(query)
# Invalid input leads to an error
{:error, changeset} =
ArticleFilter.build_filter(%{
"title_eq" => 23
})
dbg(changeset)
nil