Rails to Phoenix: querying with Ecto

In my previous post, I took a look at Ecto repositories, models, and changesets. This post will continue exploring Ecto - in particular, working with queries. We'll be using the simple Post and Rating data model from the previous post, available here.

Ecto makes heavy use of macros for query construction. The result is a query syntax that feels like you're writing SQL, embedded directly in the language. At first, it feels strange and different, but once you get accustomed, it proves its flexibility and elegance.

Some basic queries

Let's start out by querying for Post model. Open up iex with iex -S mix, and try out the following:

import Ecto.Query
query = from EctoBlog.Post

We're using the from/2 to define a query, passing a model. Notice how we create a query separately from executing it; in Ecto, queries are data. Let's add a simple where clause to the query:

query = from p in EctoBlog.Post, where: p.id == 1

We added a second argument to from. It becomes clearer when we add the unnecessary parens and brackets:

query = from(p in EctoBlog.Post, [where: p.id == 1])

The first argument is an expression defining the source of the query. The p in part of that expression sets up a binding that we can use later. As we saw earlier, it's not required; we only added it so we could reference the id field in our where clause.

The second argument is a keyword list - this is why Ecto refers to from as the "keywords query syntax". This list lets us customize the various parts of the query. In this case, we're adding a where clause, using the p binding earlier to limit our results. Ecto locates the id field of the EctoBlog.Post model through the p binding, and correctly constructs the query.

We hard-coded the argument to our where clause. It would be more realistic that our id would be dynamic. Let's try to do that:

post_id = 2
query = from p in EctoBlog.Post, where: p.id == post_id

When we try to do this, Ecto raises an Ecto.Query.CompileError. When passing values into queries, Ecto expects them to be prefixed with the pin operator (^). In this case, we should have typed:

query = from p in EctoBlog.Post, where: p.id == ^post_id
Digging deeper

So far, our queries have been simple and would be easy to express in ActiveRecord. Let's create a more complicated query. Imagine we want to know the average rating and number of ratings for each post. In ActiveRecord, we could do something like this:

  .joins("LEFT JOIN ratings ON posts.id = ratings.post_id")
  .select("posts.*, count(ratings.id) as num_ratings, avg(ratings.value) as average_rating")

We can really see ActiveRecord start to falter here. Its query requires extensive raw SQL, and it awkwardly lumps the selected num_ratings and average_rating as attributes on the resulting post models. Here's how we could write the same query with Ecto:

query = from p in EctoBlog.Post, 
  left_join: r in assoc(p, :ratings), 
  select: {p, avg(r.value), count(r.id)}, group_by: p.id

This query really starts to show the expressive power of macros. Ecto allows us to do the left join and call the SQL avg and count functions in Elixir code. The select lets us specify a custom type for our result. In this case, our query will return a list of three element tuples.

Since we don't have to use raw SQL, Ecto can have a deeper understanding of our query. If we had misspelled "count" in our ActiveRecord query, we wouldn't find out about the error until after we sent it to the database. In Ecto, the query wouldn't even compile. In a similar manner, Ecto detects if your query references a field that does not exist, or if you're passing a value into a query that is of the inappropriate type:

query = from p in EctoBlog.Post, where: p.nonexistent == 1
query = from p in EctoBlog.Post, where: p.title == 1

When we try to execute either query, an error is raised before querying the database. Ecto uses the schema we've defined to make sure the fields in our query exist, and that the types all line up. This is one of the advantages of explicitly defining the schema in the model, rather than inferring it from the database.

Composing queries

Ecto also provides a set of macros to incrementally construct queries. In fact, queries written with from actually delegate to this macro API. Here's what the queries would look like if we expressed them in the lower-level macros:

query = EctoBlog.Post |> where([p], p.id == 1)
query = EctoBlog.Post |> 
  join(:left, [p], r in EctoBlog.Rating, r.post_id == p.id) |> 
  select([p, r], {p, avg(r.value), count(r.id)}) |> 
  group_by([p, r], p.id)

Each of these macros takes an existing query as the first argument and return an updated query, so the pipeline operator lets us chain them together elegantly. The second argument represents the bindings that represent the models we're querying. Since each macro is called independently, we need to declare the bindings for each call, and the order is important.


Let's rewrite the previous example without the pipe operator:

q = EctoBlog.Post
q = join(q, :left, [p], r in EctoBlog.Rating, r.post_id == p.id)
q = select(q, [p, r], {p, avg(r.value), count(r.id)})
q = group_by(q, [p, r], p.id)

Notice that we pass an Ecto.Model into join, but select receives an Ecto.Query as its first argument. Ecto's macros don't just take models, but can accept anything that implements the Ecto.Queryable protocol. Models implement this, but Ecto.Query does as well. We can explore this further in iex:

query = EctoBlog.Post |> where([p], p.title == "Hello")
#Ecto.Query<from p in EctoBlog.Post, where: p.title == "Hello">
query |> order_by([p], desc: p.inserted_at) 
#Ecto.Query<from p in EctoBlog.Post, 
# where: p.title == "Hello", order_by: [desc: p.inserted_at]>

Looking at the output from iex, we can see that we're performing a series of functional transformations on query structs. Similarly, the from macro takes any queryable on the right side of in, so we can compose with it in a similar way:

query = from r in EctoBlog.Rating, where: r.value > 3
updated_query = from r in query, order_by: r.inserted_at

Using this composition technique, we can chain together queries in a style like we would when composing ActiveRecord scopes. In post.ex, add:

def highly_rated(query) do
  from p in query,
    left_join: r in assoc(p, :ratings),
    having: avg(r.value) > 4, group_by: p.id

def most_recent(query) do
  from p in query,
    order_by: [desc: p.inserted_at], limit: 5

Within iex, you could compose these queries:

alias EctoBlog.Post
Post |> Post.highly_rated |> Post.most_recent |> EctoBlog.Repo.all

Thanks go to Drew Olson for this technique from his post on composable queries with Ecto.

Wrapping up

I hope this has been a helpful introduction to Ecto queries. If you want to learn more about queries, documentation is available on the query DSL as well as functions that are allowed inside of queries.