SELECT date_trunc('day', date):: date as date,
(SELECT COUNT(*) FROM tasks WHERE date>="canceled") "canceled",
(SELECT COUNT(*) FROM tasks WHERE date>="done" AND (date<"canceled" or canceled isnull)) "done"
FROM generate_series('2020-01-01', '2020-01-10', '1 day'::interval) AS date
ORDER BY date
And I spent a lot of time googling if it is possible to wrap it in ecto query form without any success.
The main problem is I can’t write something like from( date in fragment(...)) and I can’t see how I can reformulate this query using joins to be able to fit into ecto restrictions on fragment() usage, because subqueries are using “less-than” or “more-than” comparisons of dates.
I found a workaround for me: for this exact case it is better to use AyeSQL instead of Ecto.
first of all, it allows me to have much more readable code (at least for me: it’s almost plain SQL) and, at last but not least, it’s working for my case
With AyeSQL, I’m able to dynamically combine all my filter cases inside pretty complex queries.