|
Hed posted:I've never been able to "bridge" the Django and SQL worlds well, probably because my SQL skills are so crummy. It's not, it's because Django's QuerySet API just doesn't translate well to SQL. You can do some moderately complex things if you try hard enough but in many cases if you wanna actually use SQL's power you gotta write raw queries as strings which sucks. ORM's are always like this, the API is just the wrong abstraction. If you're a SQL-knower what you want instead is a query builder that directly manipulates SQL syntax fragments like SQLAlchemy can. I complained about ORM's in the SQL thread about a year ago. In your first example the "prefer final" could maybe be done with select distinct on (reference_date) reference_date, name ... order by reference_date, name and Django may be able to generate that without too much fuss. In pure SQL you could just mess around with CTE's or lateral joins or whatever but not in Django. The second one should be possible I think with the strategy you describe, you can add SQL expressions to querysets IIRC, but every time I've tried to generate something moderately weird with Django it just becomes really ugly and figuring out the correct syntax takes ages.
|
#
¿
Jun 2, 2023 17:56
|
|
|
|
| # ¿ Apr 29, 2024 13:10 |
|
|
duck monster posted:Oh not realy. Get your head around Q expressions, and how Djangos lazy vs eager realtional stuff actually works and you can get a pretty good intuition to how the Queryset api maps to SQL and vice versa. I've done it extensively and it sucks, sorry. Even when you can accomplish what you want, it usually gets really hard to read. Like, sure, you can do a semi-join with where exists(<subquery>), but it just looks awful. And yeah, you can bend over backwards and sprinkle .annotate() and .aggregate() all over the place but it gets extremely cryptic and difficult to understand what the SQL would look like (multiple aggregations in the same query almost never do what you want and you can't access postgres' sum() filter(where <condition>) either). There is just not enough control over joins in general. If you want to write even moderately complex queries where you start using CTE's to make your subqueries more readable, it's time to give up. God forbid you want to do something more elaborate like do a lateral join, use a window function or join on a function (in postgres, cross join generate_series(...) is very useful for various daily/monthly reporting nonsense). To add insult to injury, even if you can generate the query, mapping the result back to a useful data structure that isn't a model class is just something you have to do yourself. I don't actually dislike Django in general, though. I worked with it for years and most of the time it's a highly productive environment. I criticize it harshly in this specific area precisely because I've tried really hard to push it to its limits. Still, it has some immense strengths, it's incredibly mature, and in many areas other than query generation it's very well designed. I've missed so much stuff Django just comes with as a builtin in other languages and frameworks. I've used Django's transaction manager as a template for implementing something similar in Golang, and I've pointed to the way Django explicitly declares migration dependencies as The Right Thing To Do in various internet arguments (even major players like Flyway don't get that bit right). The query generation is really good enough for probably a good 80% of the queries you typically write, and it's pretty easy to use. When you do learn SQL really well though and you want to really leverage all of the neat things postgres can do, Django just isn't the right tool for the job. Every ORM has the same problem, Django maybe to a slightly lesser extent than many others because it's been around for so long, but it's still the same fundamental problem.
|
|
#
¿
Jun 13, 2023 20:32
|
|