mlesin
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.
Marked As Solved
idi527
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()
Also Liked
mlesin
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.
Popular in Questions
Other popular topics
Latest on Elixir Forum
Sponsor Spotlight
Producing high quality Elixir screencasts since 2017.
Categories:
Sub Categories:
Forums
Our Sponsors
Build Elixir applications with speed and confidence.
Supporting innovation across the BEAM ecosystem.
We build reliable cloud platforms for business-critical systems.
Catch errors, track performance, monitor hosts and more.
Real-time error tracking, performance insights, and observability for devs.
Practical resources that improve the lives of professional developers.
Develop your skills with books, videos, and courses.
Producing high quality Elixir screencasts since 2017.
Courses that'll move you from confusion to "Aha, now I get it!"