Ecto: select from generated_series only

Maybe you can express generate_series('2020-01-01', '2020-01-10', '1 day'::interval) as a CTE with Ecto.Query — Ecto v3.14.0.

"date"
|> with_cte("date", as: fragment("select generate_series('2020-01-01', '2020-01-10', '1 day'::interval) as date"))
|> select([d], %{date: fragment("date_trunc('day', ?)", d.date), cancelled: fragment("select 1")})
|> Repo.all()

While actual question was answered, more wide problem now appear.

As I see, I can’t put subquery(..) in select: part, so only way to do a query like original one is to use fragment("SELECT COUNT(*) FROM...").

But, using fragment this way, I can’t add dynamic conditions inside it - but I need it to implement optional filtering.

Is there any trick or workaround to insert dynamically generated conditions inside these SELECT COUNT(*) subqueries?

By the way, is there any reason why subqueries are not allowed inside select in ecto? That would make life much easier with this kind of problems.

Maybe you could try using (lateral) joins to express the subselects? Or maybe a windowing function …

Lateral joins would still be fragments, though.

You probably want to solve this with a combination of join, subquery and select_merge, Ecto has a hard time doing the right thing with one select.

/edit

I recently solved an even more complex query than this one (but very similiar), and Ecto limitations applies to this as well.

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.