In Doctrine the query builder can take objects that describe what you want to do [1], not the best but still way better to read and understand. There's also the DQL which is an SQL-like language that's pretty well integrated in phpstorm and is quite close to SQL [2]

[1] https://www.doctrine-project.org/projects/doctrine-collectio... (for collections but you can use them for queries too)

[2] https://www.doctrine-project.org/projects/doctrine-orm/en/3.... / https://www.doctrine-project.org/projects/doctrine-orm/en/3....

The double underscore kwargs are a bit odd, I agree, but once you know about them they're okay.

And, rather like the petrol engine, it turns out while it sucks, everything else is massively worse in some vitally important way.

I've used django-orm at my previous job for 2 years and I never have liked it, the syntax is just not nice to read. I used Doctrine for 5 years (2 before last job and 3 since I got my current one) and it's just night and day. Declaring entity is just way cleaner, you can just skim through an EntityRepository / query and understand easily what it does

Having basic defensive programming, some simple classes instead of dicts everywhere and avoiding n+1 is a "different world"?

You misunderstand. And that is exactly the problem.

We did do that and that's why our queries ended up being several lines long. But if you missed just one model? You openly walk a knife again.

It's a mess and it only gets longer and longer. I ended the project with having some proper aggregates, only for that to be thrown out of the window by the guy after me.

It still selects all fields by default. Very often I have to use defer() or only() to get rid of expensive columns like blob/text that are rarely used and greatly hurt performance when grabbing them.

Then it got to where I had to make a reflective function that I use like Model.objects.defer(*all_fields_except(Model, ['field1', 'field2'])), and then add another all_fields_except() for every select_related and prefetch_related.

Even save() by default re-writes every single field. You have to use save(update_fields=['field1']) instead.

> The one thing I really appreciate with the ORM is that you really can get the ORM to make... more or less any sort of SQL query you want.

And if you can't make the ORM make the SQL query you want, you can just write it as a SQL query, like this godawful monstrosity:

      x = Site.objects.raw("select id, name, lat, lon, 111.045*degrees(acos(cos( \
        radians(latpoint))*cos(radians(lat)) \
        *cos(radians(lngpoint)-radians(lon)) \
        +sin(radians(latpoint))*sin(radians(lat)))) \
        as distance from sites_site join \
        (select %s as latpoint, %s as lngpoint) as p on 1=1 \
        order by distance limit 5", [float(lat), float(lon)])

I am in roughly equal parts proud of and horrified by this creation.

Elixir and Phoenix.

The other thing that I think people tend to forget is that there are plenty of situations where N+1 queries just aren't that big a deal. Not every view in every application that every developer builds needs to handle massive amounts of traffic with low latency and high cardinality tables. I've built so many apps where there's one or two users, small amounts of data, etc. And even on apps that do have a lot of traffic, there are often internal/admin/maintenance views that don't have the same requirements and no one will notice an N+1 where N = 5 in the worst possible case.

Every time ORMs get discussed, it seems to be dominated by people who are like "but my app has 5 billion concurrent users doing 2 million requests per second and if there's an extra 5ms on my requests, it will all explode!" and can't comprehend that not everyone is building the same kind of systems all the time. Great, maybe an ORM isn't appropriate for your situation.

Django allowing queries to be anywhere is more or less in line with Python’s overarching “we’re all consenting adults here” ethos. There’s probably one correct way to do it, but if you want to shoot yourself in the foot then here’s your gun.

It definitely takes a bit of discipline. The key layers are somewhat easy to manage—middleware, context processors, views, template tags—but I’ve seen some hairy lasagne further obscuring where the queries happen on top of that. A well-documented abstraction can be useful, but if it is possible to keep it simple and obvious then that’s the way to go.

(Third-party dependencies can further complicate things, but at least you can expect a library using ORM to be in the installed apps list.)