It's the translation of objects to SQL queries that's the problem. ORMs typically output inefficient and mostly obfuscated SQL, when a human can do a better job of hand-writing the SQL, knowing the data usage patterns needed.
The cognitive load (with some more than others) of this can be extreme. I'm thinking of Hibernate I'm particular.
I think there's a good underlying drive for this: type safe, composable, declarative language-centric queries. Especially helpful with bulk data management. I think if SQL was a more natural mapping to this model, it wouldn't be so bad, but SQL was not created for programmatic interaction (it was created to be hand written), and can be challenging to create abstractions around.
These days I'll lean on simple ORM-light tools, but quickly prefer an escape hatch when things get even a little complex. Mapping the output is something that I'm happy to delegate to a framework though.
Some tools can even provide compile time type checking of your queries, I think that's getting somewhere more interesting.
ORMs generally have a "raw" escape route where you can just do it yourself? Or maybe it's just the one I use..
Just use an ORM for mapping and for simple queries and hand crank the complex queries.
Honestly, ORMs are just an abstraction. They come at a cost and they’re not a silver bullet, just like most abstractions. I believe the hate for ORMs in many cases is due to a lack of understanding/wrong expectations.