Composable Ecto Queries in Action

August 31, 2017
Robert Beene
Managing Partner

Composable Ecto Queries in Action

Applications present information based on who you are as much as what you ask for. Let’s take a look at how this might manifest in a Phoenix 1.3 application.

Example App

Our example is a simple one for managing organizations. In the database schema below, we have the following tables.

  • companies
  • users
  • addresses
  • appointments

Features to note:

  • A Company has_many Users
  • A User belongs_to another User (manager)
  • A User may have one Address (has_one relationship)
  • A User has_many appointments


Every user will have a role. The role dictates permissions of who you can see.

  • admin - internal administrator of the system. Can see the world.
  • admin_manager - Can see everyone in their company.
  • manager - Can see anyone who has their user_id listed in manager_id field or themselves
  • direct_report - Can see themselves only.

Note: Although we won’t be reviewing it, the app must support login and require authorization for most API calls. We’ll use Guardian for this.

Use Case

The main use case of our API is the retrieval of users based on the following criteria:

  • List all users
  • List all users with a particular title
  • List users by gender
  • List users in a given state
  • List users by title

Before continuing, think about how you have done this in the past and how you might do it leveraging Ecto and the pattern matching capabilities of Elixir.

If you want to build out your solution alongside the article, you can find the foundation of this project here. This branch contains everything you need to get started including: Guardian setup, seeds, and a Postman test collection. With the test collection, you can log in as one of the seed users and test out your code.


Import Collection

Step 1. Controller setup

In order to keep things simple, I avoid forking code paths until necessary. We could pattern match in the controller, but this creates multiple code paths very early on.

def index(conn, params) do
  users = Account.list_users(conn.assigns.current_user, params)
  render(conn, "index.json", users: users)

The context for users is Account. We’ll use the boilerplate list_users function but pass in the logged in user along with the params.

If you haven’t learned about contexts for Phoenix 1.3, watch Chris McCord’s keynote from Lonestar Elixir.

Step 2. Pattern Matching in Action

You’ve either said this yourself or heard a podcast/talk where someone said it.

Anytime I find myself writing an if statement, I feel like I’m doing something wrong.

Let’s avoid that by leveraging pattern matching to follow our business rules around user visibility based on role.

def list_users(user, params) do
  User.list_users(user, user.role, params)

Before we call the User function list_users, we add an additional parameter. By adding user.role, we can pattern match on it to filter the User universe down.

def list_users(_user, "admin", params) do
  |> Repo.all()

If you’re an admin user, you get the entire User universe. What about admin_manager? Remember, these users can see anyone in their company.

Take a moment to write out a version of the function above that will pattern match on admin_manager. This function should restrict the user to their company. Refer to the schema above.

I’ll wait.

def list_users(user, "admin_manager", params) do
  |> join(:inner, [u], c in assoc(u, :company))
  |> where([_u, company], == ^user.company_id)
  |> Repo.all()

|> join(:inner, [u], c in assoc(u, :company))

By doing an inner join, we’re able to filter users to only the company of the logged in user. The query binding [u] points back to the users while c in assoc(u, :company) brings brings in the appropriate table.

|> where([_u, company], == ^user.company_id)

This where clause now has two query bindings. The first one for users is no longer needed so we can prefix the u with an underscore. With the company query binding, we can enforce that the matches user.company_id. Note the ^ prefix. This pin operator ensures the value of user.company_id is interpolated for the query.

|> Repo.all()

Now we can pipe our query to Repo.all() and get our results back.


Take a few minutes to write out functions for manager and direct_report roles.

With those functions now written, we can tackle filtering a user by state.

The API call we’d like to make would look like this.


Let’s combine what we’ve learned so far to write a function within the User module to do the above.

def by_state(query, %{"state" => state}) do
  |> join(:inner, [u], address in assoc(u, :address))
  |> where([u, address], address.state == ^state)

By pattern matching the second argument, we can extract the state and perform the necessary query. For querying by title, you would simply pattern match on that parameter and add the appropriate where clause.

You may have noticed we haven’t written any function definitions where the pattern doesn’t match. When it doesn’t, we can simply pass back the query.

  def by_state(query, _params), do: query

By placing these versions of the function after (remember - order matters) the ones that match specific query parameters, we can let the query simply slide through untouched.

Updated list_users Function

Now that we have a by_state function, we can update our original admin role to filter based on them.

def list_users(_user, "admin", params) do
  |> by_state(params)
  |> Repo.all()

If an admin submits an API request with or without a state param, we can respond accordingly. Leveraging this technique allows the code to build up a query piecemeal. However, one problem can arise with this technique. What happens if we wanted to filter in multiple ways that involve joining multiple tables? For example, if an admin manager requests users in “Ohio”, what would happen?

As an admin manager, my user list will be scoped by doing a join between users and companies. Our current version of by_state expects only two query bindings. How do we tell Ecto the bindings if we are dynamically building the query? Named bindings aren’t yet in Ecto so that’s not an option.

We’ll need to update our queries to use an ellipse. Whenever we’re building a query, we’ll know the first query binding (for User) and we’ll know the last – the one we’re adding in the function. It’s the middle that we don’t know. We might have zero or several.

def by_state(query, %{"state" => state}) do
  |> join(:inner, [u], address in assoc(u, :address))
  |> where([u, ..., address], address.state == ^state)

Want to find out more about how this change came about? Read More

With this update, our queries become truly composable. Not only can we respond to API requests per the spec, we can mix and match these. You can begin to think about each filter as a building block upon which we can craft complex queries.

For example, we could write a query that looks for users at a specific company with the title of “engineer” who reported to a manager with the ID 1. This approach allow us to create a filter – one that gradually scopes things down until we’re left with the result set we want.

Take some time and work out solutions for the following.

  • add a filter for finding users who have had an appointment with a dentist
  • refactor the various filters to be called by one function from list_users


When it comes to solving complex problems, the simpler we can make the solution the better. In our case, we’ve taken a query and broken it down to the basics and made it composable. Next time our application needs to filter by another attribute, it’ll be as simple as adding two functions (one that matches a param and filters and one that does nothing) and updating our filtering function to call it.

Combining pattern matching and composable queries proves to be a powerful combination.

You can find my completed solution here.