How to Compose Queries in Ecto
I’ve been working with Ecto for a little bit now and I keep finding that it can be really helpful to make my queries composable. By that, I mean creating functions that take in a query and return a query so that I can pipe multiple of these query functions together.
Recently, I ran into an issue that I hadn’t seen before and took me some time to get through. The issue was having multiple parts of my query join to different tables (or even join through the same table!). If you want to skip to that, just jump to the end of the blog post Composing With Multiple Joins.
tl;dr Using the as: option when joining and checking to see if a query already has a join with has_named_binding?/2 allows you to compose multiple queries that need to do joins
For everyone else, I’m going to walk through building composable queries from a single query in this post. And to do that, let’s take a look at some cars.
I’m going to be running my queries against a project I’ve made for this blog post called EctoCars. Follow the steps for the Getting Started section of the repo readme if you want to follow along!
This image is to help you understand the database domain we’ll be making queries against. There are different transmissions (automatic, manual) and engines (120hp, 250hp). A specification has a transmission and an engine. A car has a specification, a color, a vin number (we don’t need that for this, but it’s just a unique identifier), and a name.
Simple Queries
Alright, let’s start this off with a nice simple query. Let’s just find all of the blue cars. For this, we really just need Ecto.Query.where/3. I like using the expressions syntax as opposed to the keyword syntax for nice pipe-ability, so we’ll be looking at those examples moving forward. Now I said I wanted to find all the blue cars, but lets make this function a little more reusable so that it can take in any color. To do that, we could have something like this:
Now to try this out, let’s hop into our IEX session and let’s try out this query by running EctoCars.Car.with_color(“blue”) |> EctoCars.Repo.all():
iex(1)> EctoCars.Car.with_color("blue") |> EctoCars.Repo.all()18:07:08.057 [debug] QUERY OK source="cars" db=0.6ms decode=1.1ms queue=1.1ms
SELECT c0."id", c0."color", c0."vin_number", c0."specification_id" FROM "cars" AS c0 WHERE (c0."color" = $1) ["blue"]
[
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 1,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 3,
vin_number: "too_small"
},
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 3,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 1,
vin_number: "my_dream_car"
},
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 4,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 2,
vin_number: "cant_drive_this"
}
]
This is looking great, but I really just want the one ideal car for me, not three. Go ahead and try out the query with other colors if you like!
Querying with a join
Now let’s make our query a little more complex. I can’t really drive a manual car (the last time I tried was with a car with a dead battery and the guy at the lot did not like having to get out and jump the car so many times…), so it’s probably best if I look for an automatic car.
To accomplish this, we’re going to have to join to our transmissions table through our specifications table:
You’ll notice here we actually had to join twice to accomplish this. Once through specification and then again to transmission. We again made this query function re-usable by accepting the type of transmission as an argument and then using that in the where clause at the end.
One thing to make sure to be aware of is that the list of letters that we are using as the third argument to join are just variables that are bound. In the second join for example, there is nothing special about using [c, s]. Those letters make sense to us because they were the same letters used in the previous function call. If we changed the s in the second join to another letter, it would not change the query at all. (This will be more important later!)
When we try this out in the console, we see the following:
iex(3)> EctoCars.Car.with_transmission("automatic") |> EctoCars.Repo.all()16:33:16.127 [debug] QUERY OK source="cars" db=3.3ms decode=1.4ms queue=2.8ms
SELECT c0."id", c0."color", c0."vin_number", c0."specification_id" FROM "cars" AS c0 LEFT OUTER JOIN "specifications" AS s1 ON c0."specification_id" = s1."id" LEFT OUTER JOIN "transmissions" AS t2 ON s1."transmission_id" = t2."id" WHERE (t2."type" = $1) ["automatic"]
[
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 3,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 1,
vin_number: "my_dream_car"
},
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "red",
id: 2,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 1,
vin_number: "wrong_color"
},
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 1,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 3,
vin_number: "too_small"
}
]
Perfect! Just what we were looking for, all of the automatic cars. Again though, too many cars to choose from.
Making it composable
Now what if I’m really looking for all cars with an automatic transmission that are also blue. Well, instead of writing a whole new query, it would be great if we could just reuse the two existing ones.
To do this, we’re going to have to refactor the functions a bit to not just take in an argument of type/color, but also an existing query to add on to:
By making the first argument optional, we can choose to pass in a query we’re in the process of building, or we’ll just default to querying off of the cars table. We can now chain these as if we were just building one long Ecto query: EctoCars.Car |> EctoCars.Car.with_color(“blue”) |> EctoCars.Car.with_transmission(“automatic”) |> EctoCars.Repo.all():
iex(5)> EctoCars.Car |> EctoCars.Car.with_color("blue") |> EctoCars.Car.with_transmission("automatic") |> EctoCars.Repo.all()16:43:18.287 [debug] QUERY OK source="cars" db=4.0ms queue=9.5ms
SELECT c0."id", c0."color", c0."vin_number", c0."specification_id" FROM "cars" AS c0 LEFT OUTER JOIN "specifications" AS s1 ON c0."specification_id" = s1."id" LEFT OUTER JOIN "transmissions" AS t2 ON s1."transmission_id" = t2."id" WHERE (c0."color" = $1) AND (t2."type" = $2) ["blue", "automatic"]
[
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 1,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 3,
vin_number: "too_small"
},
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 3,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 1,
vin_number: "my_dream_car"
}
]
And there we have it! A composable query that finds me the car I’m looking for. But what about the cars engine? Maybe I want a car with more than 200 horse power.
Composing with multiple joins
So following along with what we were doing before, let’s create a query function for the engine:
Now if we chain it all together…
iex(10)> EctoCars.Car |> EctoCars.Car.with_color("blue") |> EctoCars.Car.with_transmission("automatic") |> EctoCars.Car.with_engine_horse_power(200) |> EctoCars.Repo.all()
** (Ecto.QueryError) lib/ecto_cars/cars/car.ex:37: field `horse_power` in `where` does not exist in schema EctoCars.Transmission in query:from c0 in EctoCars.Car,
left_join: s1 in EctoCars.Specification,
on: c0.specification_id == s1.id,
left_join: t2 in EctoCars.Transmission,
on: s1.transmission_id == t2.id,
left_join: s3 in EctoCars.Specification,
on: c0.specification_id == s3.id,
left_join: e4 in EctoCars.Engine,
on: s1.engine_id == e4.id,
where: c0.color == ^"blue",
where: t2.type == ^"automatic",
where: t2.horse_power > ^200,
select: c0(elixir) lib/enum.ex:1925: Enum."-reduce/3-lists^foldl/2-0-"/3
(elixir) lib/enum.ex:1418: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
(elixir) lib/enum.ex:1418: Enum."-map_reduce/3-lists^mapfoldl/2-0-"/3
… we get this really awesome error.
Let’s see if we can break down what happened here. Looking at the error, it’s saying that horse_power does not exist on the Transmission schema. Fair enough, but I thought we were querying the Engine schema here, right? Well if we look through the query we can see what happened a bit more. First we join to specifications (s1), then to transmission (t2), then to specifications (s3) again (that doesn’t seem great…) and then to engines(e4). Now in the where clause for horse power, we’re looking at t2 instead of e4!!
To understand this a bit better, let’s unwrap all these query pieces and just write them out together outside of their functions:
Maybe its a little easier to see here. Because of the default way bindings work with join , the table they are referencing is entirely related to it’s position in that list. So on line 8, when we are intending to query on the engines table, we are really querying against the third table in this query… transmissions! To get this to work as is, we would have to to the following:
First off, I removed the double join to Specification, because really, who needs that. I also updated the join and query to engine so that the bindings list is aware of the existing binding to Transmission. On the plus side, this will work, on the down side, this really kills our composability. As it stands, this query is now dependent on previous queries having been done (to Specification) and that the same exact number of tables were joined to the query before calling this one. There has to be a better way here so that we can keep living in our super cool composable world…
Named Bindings
Named bindings! Having to rely solely on the order that we joined to different tables is bananas. To use named bindings we pass the as option when calling join : join(query, :left, [c], s in Specification, as: :specifications, on: c.specification_id == s.id). We can then bind our specifications variable later on in the query by using the key specifications: : join(query, :left, [c, specifications: s], t in Transmission, as: :transmissions, on: s.transmission_id == t.id). Now let’s go ahead and re-write our functions to use named bindings:
I’m not going to show the output from testing each of these individually, but I promise they work ;) But something interesting happens when we try and chain them again…
iex(16)> EctoCars.Car |> EctoCars.Car.with_color("blue") |> EctoCars.Car.with_transmission("automatic") |> EctoCars.Car.with_engine_horse_power(200) |> EctoCars.Repo.all()
** (Ecto.Query.CompileError) alias `:specifications` already exists
(ecto_cars) lib/ecto_cars/cars/car.ex:35: EctoCars.Car.with_engine_horse_power/2Ok, a new error! It looks like we cannot re-bind the same key (makes sense…). A simple solution would be to take out the join to specifications in the with_engine_horse_power function, but then we are again stuck in a place where this function would have to be called only after a function that joins to specifications was called.
I think the best option here is to instead check and see if the query has already joined to a specific table and only re-join if it has not. We can neatly accomplish this with has_named_binding?. This function allows us to do just that, check and see if a query already has that binding. By making a smaller helper function, we should be in good shape. Let’s see what that refactor looks like:
The join_specifications helper function is in charge of checking for the join and adding it to the query if needed. We can now finally write a composable query that does not rely on any specific order or rely on one function always needing to be called before a second can be called!
iex(17)> EctoCars.Car |> EctoCars.Car.with_color("blue") |> EctoCars.Car.with_transmission("automatic") |> EctoCars.Car.with_engine_horse_power(200) |> EctoCars.Repo.all()17:31:25.303 [debug] QUERY OK source="cars" db=2.6ms queue=3.8ms
SELECT c0."id", c0."color", c0."vin_number", c0."specification_id" FROM "cars" AS c0 LEFT OUTER JOIN "specifications" AS s1 ON c0."specification_id" = s1."id" LEFT OUTER JOIN "transmissions" AS t2 ON s1."transmission_id" = t2."id" LEFT OUTER JOIN "engines" AS e3 ON s1."engine_id" = e3."id" WHERE (c0."color" = $1) AND (t2."type" = $2) AND (e3."horse_power" > $3) ["blue", "automatic", 200]
[
%EctoCars.Car{
__meta__: #Ecto.Schema.Metadata<:loaded, "cars">,
color: "blue",
id: 3,
specification: #Ecto.Association.NotLoaded<association :specification is not loaded>,
specification_id: 1,
vin_number: "my_dream_car"
}
]
And there we have it! I’ve found my perfect car with the help of composable queries.
References:
- Programing Ecto: Build Database Apps in Elixir for Scalability and Performance
Thanks for reading! To learn more about Flatiron School, visit the website, follow us on Facebook and Twitter, and visit us at upcoming events near you.
