In some occasions, you might need to use alias in your Ecto query. For example, while developing TIL, I need to use both date_trunc (PostgreSQL) together with group_by.

In SQL, it can be written as:

select date_trunc('day', inserted_at) as date 
from "posts" 
group by date;

In Ecto, you can use fragment to call database function. Hence, to use date_trunc, we can just:

from q in Post,
select: [fragment("date_trunc('day', ?) as date", q.inserted_at],
group_by: :date

However, this will raise an error mentioning that date column is not available for Post table. Hence, we need a way to use the SQL aliases date in the query.

To use SQL alias in a group_by, we can simply use fragment again.

from q in Post,
select: [fragment("date_trunc('day', ?) as date", q.inserted_at],
group_by: fragment("date")

I was able to solve this thanks to this Stack Overflow question.

To use raw SQL with ecto, we can use Repo.query. For example:

Repo.query("SELECT * from users;"

# Returns
{:ok,
 %Postgrex.Result{
   columns: ["id", "email"],
   command: :select,
   connection_id: 73530,
   messages: [],
   num_rows: 1,
   rows: [
     [1, "[email protected]"]
   ]
 }}

You might be wondering that why you can’t find Repo.query in the Ecto.Repo documentation.

It is actually documented under Ecto.Adapters.SQL documentation since this function is implemented in Ecto.Adapters.SQL and is make available to repository module for convenience.

When using Ecto in Elixir/Phoenix application, there are a few approaches we can use to fetch the first record ordered by specific column.

Using limit and order_by

We can use a combination of Ecto.Query.limit, Ecto.Query.order_by and Ecto.Repo.one to get the first record:

query = from p in Post, order_by: :id, limit: 1
Repo.one(query)

Depends on the requirement of ordering, we can also use order_by: [desc: :id] to sort the table in descending order and get the first result.

Using first or last

The above approach can be quite verbose. Fortunately, Ecto.Query also have first() and last() function that restrict the query to return the first or last result ordered default by primary key.

Both function will automatically set the query limit to 1. We can also provide order_by parameter these functions to specify the column to be used for ordering.

Post |> first() |> Repo.one()
Post |> first(:views) |> Repo.one()

For more details on the functions, you can refer to:

• Ecto.Query.first/2
• Ecto.Query.last/2