#31: Composing Ecto Queries

Elixir 1.4.5

Phoenix 1.3

Ecto 2.1.3

View source on GitHub

---

Now this patter works really well when we want to chain queries together.

Let’s say we wanted to add the ability to filter our movies based on their genre - how could we do that?

Here we have our movie app and it’s got a new feature that helps us filter the movies displayed.

Currently we can use it to return any of our movies released after a certain year.

Now one of the great features of using Ecto for is for composable queries.

Let’s walk through how this is working and how simply we can add additional filters to it.

From our editor let’s open our movies index.html.eex template.

Here is our search form. We’re using our connection, and making a request to our movie_path. And by using as: :search we’re grouping the fields for this form in our params under the search key.

Right now we only have one field - released_after which is a select field populated with a list of years.

And if we open our movie_view.ex and we can see the released_after_years function returns our list of the years displayed in our dropdown.

Now when this form is submitted a request is made to the index action on our movie_controller.ex module, let’s open that module.

And you can see we’re using get_in to get the released_after field and assigning it to the variable year.

We’re then creating a pipeline where we we take our movie and pass it into the Movie. released_after function along with the year.

This returns an Ecto query which is then piped into Repo.all, returning all movies that match the query.

Let’s take a closer look at the query.

We’ll open our movie.ex module.

We’re using a guard clause, so when no search is performed and year is nil we just return our query.

Otherwise we use Ecto to query for all movies where the year is greater than or equal to the year passed in.

This works because Ecto queries can take either the Module OR the query.

The value on the right-side of in needs to implement the Ecto.Queryable protocol.

For our example here, this can either be an Ecto.Query or our Movie module, since modules in Elixir are really Atoms.

So another way we could look this would be to remove the query, and use Movie in it’s place.

This pattern works really well when we want to chain queries together. So let’s change our function back to use the variable query.

And now let’s say we now wanted to add the ability to filter on genres, how could we do that?

First we need to get all of our movie genres.

So let’s create another function all_genres, and we’ll allow it to accept a query as well.

Then we’ll create an Ecto query to select all distinct genres from our movies.

lib/teacher_web/models/movie.ex

…
  def all_genres(query) do
    from movie in query,
    distinct: movie.genre,
    select: movie.genre
  end
…

Then let’s go back to our movie_controller.ex

And let’s update our index function to use our new function to get our genres.

And then we’ll pass our genres into our assigns so access them in our template.

lib/teacher_web/controllers/movie_controller.ex

…
def index do
    year = get_in(params, ["search", "released_after"])
    movies = Movie
            |> Movie.released_after(year)
            |> Repo.all()
    genres = Movie
            |> Movie.all_genres()
            |> Repo.all()
    render(conn, "index.html", movies: movies, genres: genres)
end
…

So let’s go back to our movie index.html.eex template.

And we’ll add another field to our search form.

Let’s make it a select called genre. And then for the values, we’ll use our @genres list from the assigns

And we’ll include a prompt of "Choose genre".

Now when we submit our search form it will pass over our genre too.

Template path: lib/teacher_web/templates/movie/index.html.eex

  <%= form_for @conn, movie_path(@conn, :index), [method: :get, as: :search], fn f -> %>
    <%= label(f, :released_after, "After") %>
    <%= select(f, :released_after, released_after_years, prompt: "Year") %>
    <%= label(f, :genre, "Genre") %>
    <%= select(f, :genre, @genres, prompt: "Choose genre") %>
    <%= submit "Filter" %>
  <% end %>

Let’s go back to our movie_controller.ex module and now we can get our genre from the params.

Now that we have our genre, we need to use it.

Let’s create another query. We’ll open our movie.ex module. And we’ll add another function. Let’s call it by_genre that will take a query and a genre.

The we can write our Ecto query to get all movies that match the given genre.

We’ll also need to add a function to guard against when no genre is selected. And in those cases we’ll just return the query.

lib/teacher_web/models/movie.ex

…
def by_genre(query, genre) when is_nil(genre) or byte_size(genre) == 0 do
  query
end
def by_genre(query, genre) do
  from movie in query,
  where: movie.genre == ^genre
end
…

Now that we have our query, let’s go back to our movie_controller.ex module.

And we’ll compose our query by placing it along with our other query in the pipeline.

lib/teacher_web/controllers/movie_controller.ex

…
  def index(conn, params) do
    year = get_in(params, ["search", "released_after"])
    genre = get_in(params, ["search", "genre"])
    movies = Movie
            |> Movie.released_after(year)
            |> Movie.by_genre(genre)
            |> Repo.all()
    genres = Movie
             |> Movie.all_genres()
             |> Repo.all()
    render(conn, "index.html", movies: movies, genres: genres)
  end
…

With that we can open our browser.

And now we can filter our movies by genre and by year.