In this post, we'll learn how to use variables in an Ecto query to specify the order of an order by statement.

Let's say we have a list of blog posts and we have a method in our repository to retrieve them. The code looks like this:

def list_scheduled_posts() do
  from(
    p in Post,
    where: p.published_at > ^DateTime.utc_now(),
    order_by: [desc: p.published_at]
  )
  |> Repo.get()
end

This query lists all posts which have a publish date in the future, ordering them by published_at in descending order.

Now, depending on where I want to show this list, in some cases, I want to list them in ascending order, in other places, I would like to list them in descending order (or even use a query string param to decide the order).

One option is duplicate the function and change the ordering like this:

def list_scheduled_posts_desc() do
  from(
    p in Post,
    where: p.published_at > ^DateTime.utc_now(),
    order_by: [desc: p.published_at]
  )
  |> Repo.get()
end

def list_scheduled_posts_asc() do
  from(
    p in Post,
    where: p.published_at > ^DateTime.utc_now(),
    order_by: [asc: p.published_at]
  )
  |> Repo.get()
end

Even though this works, it won't scale very well. I prefer to have a single function where I can pass a parameter to define the sort direction.

Since the key in the order_by keyword list is a variable, we can use the alternate syntax:

iex(1)> a = :desc
:desc
iex(2)> [{a, :b}]
[desc: :b]
iex(3)> [{:desc, :b}] == [desc: b]
true

Knowing this, we can move everything into a single function with an argument:

def list_scheduled_posts(order_by \\ :desc) do
  from(
    p in Post,
    where: p.published_at > ^DateTime.utc_now(),
    order_by: [{^order_by, p.published_at}]
  )
  |> Repo.get()
end

That seems to be a lot better than duplicating the function.

If you wonder why we need to use the ^ in the query, it's explained in the Ecto documentation:

Interpolation and casting

External values and Elixir expressions can be injected into a query expression with ^:

def with_minimum(age, height_ft) do
  from u in "users",
    where: u.age > ^age and u.height > ^(height_ft * 3.28),
    select: u.name
end

with_minimum(18, 5.0)

When interpolating values, you may want to explicitly tell Ecto what is the expected type of the value being interpolated:

age = "18"
Repo.all(from u in "users",
          where: u.age > type(^age, :integer),
          select: u.name)

In the example above, Ecto will cast the age to type integer. When a value cannot be cast, Ecto.Query.CastError is raised.

To avoid the repetition of always specifying the types, you may define an Ecto.Schema. In such cases, Ecto will analyze your queries and automatically cast the > interpolated "age" when compared to the u.age field, as long as the age field is defined with type :integer in your schema:

age = "18"
Repo.all(from u in User, where: u.age > ^age, select: u.name)

Another advantage of using schemas is that we no longer need to specify the select option in queries, as by default Ecto will retrieve all fields specified in > the schema:

age = "18"
Repo.all(from u in User, where: u.age > ^age)

For this reason, we will use schemas on the remaining examples but remember Ecto does not require them in order to write queries.