Recently, I learnt that you can concatenate string in PostgreSQL using ||
instead of relying of CONCAT
function. For example,
SELECT firstname || ' ' || lastname
FROM users;
Tags: sql postgresql
Recently, I learnt that you can concatenate string in PostgreSQL using ||
instead of relying of CONCAT
function. For example,
SELECT firstname || ' ' || lastname
FROM users;
Tags: sql postgresql
When using Ecto in Elixir/Phoenix application, there are a few approaches we can use to fetch the first record ordered by specific column.
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.
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:
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.
Tags: ecto
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.
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)
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.
Tags: elixir