mlesin
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.
Trending in Questions
Hello everyone!
I am new to coding with Elixir/Erlang. And i am looking for some utilities for fuzzing testing like AFL++.
Does anyone ...
New
I’m very new to Elixir, so sorry if this is a silly question:
I am wondering what happens to the mailbox of a process when the machine i...
New
How do I tell Hologram not to escape HTML when substituting into templates?
{Textile.render(@product.long_description)}
escapes the HTM...
New
Background
When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error:
* assembling mark...
New
I load tested a plain Phoenix chat app backed by Postgres with 100 million messages in it, everything on default settings, to see what br...
New
I’d like to create a file with 600 permissions (rw-------), but there’s a race condition if I create it and then change the permissions. ...
New
Is there a way to import record definitions in the iex shell for pretty printing (similar to the rr command in the erl shell)? This woul...
New
Other Trending Topics
Hey there,
I haven’t seen a list of useful Elixir specific skills for the popular AI tools yet so I’m starting one here with my first.
...
New
Hi everyone :waving_hand:
I wanted to share something I’ve been building — NowBlind, a random chat app designed to help people discover ...
New
I fully migrated to my own harness from Anthropic/Gemini and I think it’s time to share it. Welcome DSH, the DeepSeek Harness, fully writ...
New
(I just needed to vent somewhere and LinkedIn is full of hope, or hype, I’m not sure which exactly)
AI dream has many faces, but general...
New
Localize is the next generation localisation library for Elixir. Think of it as ex_cldr version 3.0. The first version will be released ...
New
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New
Categories:
Sub Categories:
Forums
Popular Tags
- #ecto
- #liveview
- #troubleshooting
- #learning-elixir
- #library
- #deployment
- #erlang
- #testing
- #genserver
- #mix
- #absinthe
- #remote-other
- #otp
- #plug
- #how-to-question
- #macros
- #postgres
- #elixirconf
- #channels
- #exunit
- #code-sync
- #discussion
- #podcasts
- #javascript
- #onsite
- #dialyzer
- #docker
- #authentication
- #ai
- #elixirconf-us
- #umbrella
- #full-time-contract
- #podcasts-by-brainlid
- #blog-post
- #ecto-query
- #elixir-ls
- #phoenix_html
- #iex
- #graphql
- #genstage
- #websockets
- #supervisor
- #advent-of-code
- #distillery
- #security
- #elixirconf-eu
- #processes
- #api
- #forms
- #hex










Showing Posts 1 to 5- Show Best Posts
- Show All (oldest first)
- Show All (newest first)
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.mlesin
While actual question was answered, more wide problem now appear.
As I see, I can’t put
subquery(..)inselect:part, so only way to do a query like original one is to usefragment("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.
idi527
Maybe you could try using (lateral) joins to express the subselects? Or maybe a windowing function …
Lateral joins would still be fragments, though.
Schultzer
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.
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.