mlesin

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.

Showing Posts 1 to 5

idi527

idi527

:waving_hand:

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()
mlesin

mlesin OP

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.

idi527

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

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

mlesin OP

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.

— All posts loaded —

Where Next? Top

Trending in Questions Top

Hellowman
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
yarrichar
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
pragdave
How do I tell Hologram not to escape HTML when substituting into templates? {Textile.render(@product.long_description)} escapes the HTM...
New
Fl4m3Ph03n1x
Background When trying to execute mix release on a Windows 11 machine for a Phoenix project I get the following error: * assembling mark...
New
mandar1045
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
rlipscombe
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
saleyn
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 Top

dominicletz
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
raa-ziq
Hi everyone :waving_hand: I wanted to share something I’ve been building — NowBlind, a random chat app designed to help people discover ...
New
mudasobwa
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
OndrejValenta
(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
kip
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
jimsynz
Beam Bots (or just BB for short) is a framework for building fault-tolerant robotics applications in Elixir using familiar OTP patterns. ...
New

We're in Beta

About us Mission Statement

Options

Thread Display Mode




Thread Preview

Skip Thread Previews