Dynamic queries
Ecto was designed from the ground up to have an expressive query API that leverages Elixir syntax to write queries that are pre-compiled for performance and safety. When building queries, we may use the keywords syntax
import Ecto.Query
from p in Post,
where: p.author == "José" and p.category == "Elixir",
where: p.published_at > ^minimum_date,
order_by: [desc: p.published_at]
or the pipe-based one
import Ecto.Query
Post
|> where([p], p.author == "José" and p.category == "Elixir")
|> where([p], p.published_at > ^minimum_date)
|> order_by([p], desc: p.published_at)
While many developers prefer the pipe-based syntax, having to repeat the binding p
made it quite verbose compared to the keyword one.
Another problem with the pre-compiled query syntax is that it has limited options to compose the queries dynamically. Imagine for example a web application that provides search functionality on top of existing posts. The user should be able to specify multiple criteria, such as the author name, the post category, publishing interval, etc.
To solve those problems, Ecto also provides a data-structure centric API to build queries as well as a very powerful mechanism for dynamic queries. Let's take a look.
Focusing on data structures
Ecto provides a simpler API for both keyword and pipe based queries by making data structures first-class. Let's see an example:
from p in Post,
where: [author: "José", category: "Elixir"],
where: p.published_at > ^minimum_date,
order_by: [desc: :published_at]
and
Post
|> where(author: "José", category: "Elixir")
|> where([p], p.published_at > ^minimum_date)
|> order_by(desc: :published_at)
Notice how we were able to ditch the p
selector in most expressions. In Ecto, all constructs, from select
and order_by
to where
and group_by
, accept data structures as input. The data structure can be specified at compile-time, as above, and also dynamically at runtime, shown below:
where = [author: "José", category: "Elixir"]
order_by = [desc: :published_at]
Post
|> where(^where)
|> where([p], p.published_at > ^minimum_date)
|> order_by(^order_by)
While using data-structures already brings a good amount of flexibility to Ecto queries, not all expressions can be converted to data structures. For example, where
converts a key-value to a key == value
comparison, and therefore order-based comparisons such as p.published_at > ^minimum_date
need to be written as before.
Dynamic fragments
For cases where we cannot rely on data structures but still desire to build queries dynamically, Ecto includes the Ecto.Query.dynamic/2
macro.
The dynamic
macro allows us to conditionally build query fragments and interpolate them in the main query. For example, imagine that in the example above you may optionally filter posts by a date of publication. You could of course write it like this:
query =
Post
|> where(^where)
|> order_by(^order_by)
query =
if published_at = params["published_at"] do
where(query, [p], p.published_at < ^published_at)
else
query
end
But with dynamic fragments, you can also write it as:
where = [author: "José", category: "Elixir"]
order_by = [desc: :published_at]
filter_published_at =
if published_at = params["published_at"] do
dynamic([p], p.published_at < ^published_at)
else
true
end
Post
|> where(^where)
|> where(^filter_published_at)
|> order_by(^order_by)
The dynamic
macro allows us to build dynamic expressions that are later interpolated into the query. dynamic
expressions can also be interpolated into dynamic expressions, allowing developers to build complex expressions dynamically without hassle.
By using dynamic fragments, we can decouple the processing of parameters from the query generation. Let's see a more complex example.
Building dynamic queries
Let's go back to the original problem. We want to build a search functionality where the user can configure how to traverse all posts in many different ways. For example, the user may choose how to order the data, filter by author and category, as well as select posts published after a certain date.
To tackle this in Ecto, we can break our problem into a bunch of small functions, that build either data structures or dynamic fragments, and then we interpolate it into the query:
def filter(params) do
Post
|> order_by(^filter_order_by(params["order_by"]))
|> where(^filter_where(params))
end
def filter_order_by("published_at_desc"),
do: [desc: dynamic([p], p.published_at)]
def filter_order_by("published_at"),
do: [asc: dynamic([p], p.published_at)]
def filter_order_by(_),
do: []
def filter_where(params) do
Enum.reduce(params, dynamic(true), fn
{"author", value}, dynamic ->
dynamic([p], ^dynamic and p.author == ^value)
{"category", value}, dynamic ->
dynamic([p], ^dynamic and p.category == ^value)
{"published_at", value}, dynamic ->
dynamic([p], ^dynamic and p.published_at > ^value)
{_, _}, dynamic ->
# Not a where parameter
dynamic
end)
end
Because we were able to break our problem into smaller functions that receive regular data structures, we can use all the tools available in Elixir to work with data. For handling the order_by
parameter, it may be best to simply pattern match on the order_by
parameter. For building the where
clause, we can use reduce
to start with an empty dynamic (that always returns true) and refine it with new conditions as we traverse the parameters.
Testing also becomes simpler as we can test each function in isolation, even when using dynamic queries:
test "filter published at based on the given date" do
assert dynamic_match?(
filter_where(%{}),
"true"
)
assert dynamic_match?(
filter_where(%{"published_at" => "2010-04-17"}),
"true and q.published_at > ^\"2010-04-17\""
)
end
defp dynamic_match?(dynamic, string) do
inspect(dynamic) == "dynamic([q], #{string})"
end
In the example above, we created a small helper that allows us to assert on the dynamic contents by matching on the results of inspect(dynamic)
.
Dynamic and joins
Even query joins can be tackled dynamically. For example, let's do two modifications to the example above. Let's say we can also sort by author name ("author_name" and "author_name_desc") and at the same time let's say that authors are in a separate table, which means our authors filter in filter_where
now need to go through the join table.
Our final solution would look like this:
def filter(params) do
Post
# 1. Add named join binding
|> join(:inner, [p], assoc(p, :authors), as: :authors)
|> order_by(^filter_order_by(params["order_by"]))
|> where(^filter_where(params))
end
# 2. Returned dynamic with join binding
def filter_order_by("published_at_desc"),
do: [desc: dynamic([p], p.published_at)]
def filter_order_by("published_at"),
do: dynamic([p], p.published_at)
def filter_order_by("author_name_desc"),
do: [desc: dynamic([authors: a], a.name)]
def filter_order_by("author_name"),
do: dynamic([authors: a], a.name)
def filter_order_by(_),
do: []
# 3. Change the authors clause inside reduce
def filter_where(params) do
Enum.reduce(params, dynamic(true), fn
{"author", value}, dynamic ->
dynamic([authors: a], ^dynamic and a.name == ^value)
{"category", value}, dynamic ->
dynamic([p], ^dynamic and p.category == ^value)
{"published_at", value}, dynamic ->
dynamic([p], ^dynamic and p.published_at > ^value)
{_, _}, dynamic ->
# Not a where parameter
dynamic
end)
end
Adding more filters in the future is simply a matter of adding more clauses to the Enum.reduce/3
call in filter_where
.