Use Ecto to generate_series in postgres and also retrieve Null-values as “0”

I want to display a range of dates (statistics) like:

Dates        | Count
--------------------
"2016-09-01" | 0
"2016-09-02" | 0
"2016-09-03" | 0
"2016-09-04" | 0
"2016-09-05" | 0
"2016-09-06" | 12
"2016-09-07" | 9
"2016-09-08" | 0
"2016-09-09" | 90

The raw SQL query:

select date(d) as day, count(clicks.id)
  from generate_series(
    current_date - interval '13 day', 
    current_date, 
    '1 day'
  ) d 
left join clicks on date(clicks.inserted_at) = d and clicks.link_id = 15
group by day order by day;

I would love to use it with Ecto without the Raw SQL

The closest thing I found was :

query = from c in Like
  where: c.link_id == ^link.id

...

from( c in query,
  join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d "),
  on: fragment("date(?) = d", field(c, :inserted_at)),
  group_by: fragment("day"),
  order_by: fragment("day"),
  select: %{
    day: fragment("date(d) as day"),
    count: count(c.id)
  }
)

But it returns only the dates presented in the table and not the whole range (i.e; displaying 0 for the dates that does not have a like in that day)

Solution

You can use right_join since you're joining clicks with the generated dates in the Ecto Query instead of generated dates with clicks like you did in the raw SQL.

Test:

alias MyApp.{Click, Repo}
import Ecto.Query

from(c in Click,
  order_by: c.inserted_at,
  select: fragment("date(?) as day", c.inserted_at)
)
|> Repo.all
|> IO.inspect

from(c in Click,
  right_join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d "),
  on: fragment("date(?) = d", field(c, :inserted_at)) and c.link_id == 15,
  group_by: fragment("day"),
  order_by: fragment("day"),
  select: %{
    day: fragment("date(d) as day"),
    count: count(c.id)
  }
)
|> Repo.all
|> IO.inspect

Output:

[debug] QUERY OK source="clicks" db=1.9ms
SELECT date(c0."inserted_at") as day FROM "clicks" AS c0 ORDER BY c0."inserted_at" []
[{2016, 9, 12}, {2016, 9, 13}, {2016, 9, 13}, {2016, 9, 13}, {2016, 9, 13},
 {2016, 9, 15}, {2016, 9, 17}, {2016, 9, 17}]
[debug] QUERY OK source="clicks" db=1.8ms
SELECT date(d) as day, count(c0."id") FROM "clicks" AS c0 RIGHT OUTER JOIN (select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d ) AS f1 ON date(c0."inserted_at") = d GROUP BY day ORDER BY day []
[%{count: 0, day: {2016, 9, 8}}, %{count: 0, day: {2016, 9, 9}},
 %{count: 0, day: {2016, 9, 10}}, %{count: 0, day: {2016, 9, 11}},
 %{count: 1, day: {2016, 9, 12}}, %{count: 4, day: {2016, 9, 13}},
 %{count: 0, day: {2016, 9, 14}}, %{count: 1, day: {2016, 9, 15}},
 %{count: 0, day: {2016, 9, 16}}, %{count: 2, day: {2016, 9, 17}},
 %{count: 0, day: {2016, 9, 18}}]

Edit: You might also want to make more use of Ecto's powerful querying ability and rely less on as and fragment. The following query generates the same output:

from(c in Click,
  right_join: day in fragment("select generate_series(current_date - interval '10 day', current_date, '1 day')::date AS d"),
  on: day.d == fragment("date(?)", c.inserted_at) and c.link_id == 15,
  group_by: day.d,
  order_by: day.d,
  select: %{
    day: fragment("date(?)", day.d),
    count: count(c.id)
  }
)