March 21, 2019

If you’ve used Elixir for any significant period of time, chances are you’ve come across Ecto. This will not be a tutorial on Ecto itself. Instead, I wanted to showcase a great pattern for query composition I’ve come across at work.

Setting the Scene 🎬 #

For simplicity’s sake, we’ll be querying a single table with no associations. This table contains all players in the NBA and their details, like this:

defmodule Nba.Players.Player do use Ecto.Schema schema "nba_players" do field :first_name, :string field :last_name, :string field :team, :string field :college, :string field :height_cm, :integer field :weight_kg, :float field :salary, :integer end end

A typical Ecto query for some players in this context might look something like this:

defmodule Nba.Players.Repo do import Ecto.Query alias Nba.Players.Player alias Nba.Repo def get_players_by_team(team) do Player |> where([player], player.team == ^team) |> Repo.all() end end

First, let’s be honest - there’s nothing inherently wrong with this. Ecto’s API is so nice that even when we need to chain a number of expressions together, it’s still succinct and easy to read. The problem comes when we start repeating ourselves. As things progress, there’s no doubt we’ll be running into cases where a different query is matching on a column we’ve also matched on elsewhere.

In a perfect world, the reader shouldn’t need to know the implementation details, and the writer should have a nice library upon which they can easily build new queries for their specific domain .

Filter By, Filter Next 🌈 #

Let’s write the same query again, but this time we’ll have a Queries module handle the details:

defmodule Nba.Players.Queries do import Ecto.Query def filter_by(query, nil), do: query def filter_by(query, filters), do: Enum.reduce(filters, query, & filter_next/2) def filter_next({:team, team}, query), do: where(query, [player], player.team == ^team) end

defmodule Nba.Players.Repo do alias Nba.Players.Player alias Nba.Repo alias Nba.Players.Queries def get_players_by_team(team) do Player |> Queries.filter_by(%{team: team}) |> Repo.all() end end

Seems like more work for the same result, right? In a trivial case like this, absolutely. But, drop everything! Product just got a request from our only client: Nobody & Co.

They want to see all NBA Players who:

Are on a specific team.

Are above or equal to a certain salary.

Are below a given height.

Are above a given weight.

Have a specific last name.

It’s Nobody’s fault, it’s just gotta be done.

By utilising filter_by/2 and filter_next/2 in our new Queries module, we can make this read almost like English in our Repo:

defmodule Nba.Players.Queries do import Ecto.Query def filter_by(query, nil), do: query def filter_by(query, filters), do: Enum.reduce(filters, query, & filter_next/2) def filter_next({:team, team}, query), do: where(query, [p], p.team == ^team) def filter_next({:salary_above_or_equal, salary}, query), do: where(query, [p], p.salary >= ^salary) def filter_next({:height_below, height}, query), do: where(query, [p], p.height_cm < ^height) def filter_next({:weight_below, weight}, query), do: where(query, [p], p.weight_kg < ^weight) def filter_next({:last_name, last_name}, query), do: where(query, [p], p.last_name == ^last_name) end

defmodule Nba.Players.Repo do alias Nba.Players.Player alias Nba.Repo alias Nba.Players.Queries def query_nobody_needs(%{} = details) do Player |> Queries.filter_by(%{ team: details.team, salary_above_or_equal: details.salary, height_below: details.height, weight_below: details.weight, last_name: details.last_name }) |> Repo.all() end end

Personally, I think this is so nice - by using this pattern we can reduce even more complex queries with joins and the lot down plain English keys in a map. Try it out to see how it might improve your own Repos.

📣 📣 📣

As much as I’d like to, I can’t take credit for this approach. A quick shoutout to my co-workers at Xplor for always sharing knowledge so readily.

Thanks for reading!

4 Kudos