Hi!
I’m currently trying to build a statistics view for my users where they can get an overview of their recent usage in a graph. I want to show a per day count of “prospects”.
I’m pretty sure using postgresql ‘generate_series’ is exactly what i want to produce groupings in daily intervals.
My code so far is heavily based on a stackoverflow post I found and currently looks like:
from e in query,
right_join:
day in fragment(
"select generate_series(date(?), date(?), '1 day')::date AS d",
^from,
^to
),
on: day.d == fragment("date(?)", e.inserted_at),
group_by: day.d,
order_by: day.d,
select: %{
id: fragment("date(?)", day.d),
value: count(e.id)
}
This work well giving me for example:
[
%{id: ~D[2020-02-23], value: 0},
%{id: ~D[2020-02-24], value: 0},
%{id: ~D[2020-02-25], value: 0},
%{id: ~D[2020-02-26], value: 0},
%{id: ~D[2020-02-27], value: 0},
%{id: ~D[2020-02-28], value: 0}
]
assuming that query is simply query = from p in Prospect
But I’m having trouble getting it to fit together with the rest of my query which currently is something like
query =
from p in Prospect,
where: p.profile_id in ^profile_ids
Which causes the result to be a simple empty list. I would like to still get the empty row so to say. In lack of better sql vocabulary; I would like the where to be applied conditionally and not prevent empty rows to be generated in the series since that makes rendering graphs a lot easier.
Would be great if someone could nudge me in the right direction. Should I look at dividing it into a subquery or do I need to split this into multiple queries, or is there something simple that I’m overlooking, I’m suspecting there is some kind of join that I should be using instead…
Thank you in advance.
