Database queries with Ecto in Elixir

Database queries with Ecto in Elixir

Ecto is a database abstraction layer to do queries on sql databases. It took
me some time to figure out how to do these queries, so here a short write-up.

There are two ways to create queries, one with keywords the other with query expressions.
Here an example with keywords:

partner_id = 1
query = from t in PartnerTransaction,
join: i in Impression, on: t.id == i.open_fee_partner_transaction_id,
where: i.advertiser_id == ^partner_id,
select: count(t.id)
Repo.one(query)

So you see, we set t to PartnerTransaction, then join on Impression, select only for partner 1,
and finally return the number of entries found.

The same query with expressions is

partner_id = 1
PartnerTransaction
|> join(:inner, [t], i in Impression, t.id == i.open_fee_partner_transaction_id)
|> where([t,i], i.advertiser_id == 1)
|> select([t], count(t.id))
|> Repo.one

The main difference here is that you need to always add the tables you query on
in the expression, for instance the `[t,i]` in the where clause.