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)
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)
}
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With