Ecto: select from generated_series only

I have table “tasks” with following fields:

id:bigint
canceled:date
done:date

And I have SQL query in this form:

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.

Any suggestions are welcome.

:wave:

Maybe you can express generate_series('2020-01-01', '2020-01-10', '1 day'::interval) as a CTE with https://hexdocs.pm/ecto/Ecto.Query.html?#with_cte/3.

"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()
2 Likes

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 :slight_smile:

With AyeSQL, I’m able to dynamically combine all my filter cases inside pretty complex queries.

2 Likes