Recently, I learnt that you can concatenate string in PostgreSQL using || instead of relying of CONCAT function. For example,

SELECT firstname || ' ' || lastname
FROM users;

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

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.

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.

In Elixir, we can use the Erlang module erl_tar to compress a file into tar.gz. It is straightforward to create a tar file with erl_tar. However, there is a caveat.

Unexpected Error

Assuming that we have a file named test.md and run the following code:

# :erl_tar.create(TarFilename, [Filename], [Options])
:erl_tar.create("tarfilename.tar.gz", 
                ["test.md"], 
                [:write, :compressed])

Surprisingly, you will receive a FunctionClauseError which stated that there are no function clause matching in :erl_tar.add/4. (as for Elixir 1.8.1)

Solution

This is because the expected file name type in erl_tar seems to be charlists. It is mentioned in Elixir Official Guide that charlists are used mostly when interfacing with Erlang where some old libraries do not accept binaries as arguments. (The double quoted “string” is UTF-8 encoded binary)

To resolve the error, we need to use the single quoted file name, 'test.md'.

:erl_tar.create("tarfilename.tar.gz", 
                ['test.md'], 
                [:write, :compressed])

That’s all. Hopefully it helps!

You can also pass in a list of tuple into the second argument in the format of [{filenameInArchive, filename or binary}]. For more on :erl_tar.create refer to the documentation.