Yeah, I understand what N+1 queries are and how many ORMs make them too easy.
For me, ORMs become a problem when they're an excuse to avoid learning SQL. If you understand SQL, you will probably understand why the example you give is a bad idea. If you don't, you won't. I'm speaking from the point of view of having written, at this point, thousands of lines of SQL at minimum, and having decided that it's not how I primarily want to access data in the applications I write. The ORM queries I write are written with rough knowledge of the SQL they generate, and I try to be very careful with query builders to avoid the exact case you bring up.
I think LINQ in C# does a pretty good job of bridging this gap, actually. It could be better, but it discourages looping and encourages the declarative style that efficient SQL requires.
I think this is the problem that tools can’t solve. If you want to interface with a database, you need to know how a database works, regardless of what tools you use. I like using ORMs, and I think I write good ORM code. But I also think that I know quite a bit about how databases work, and that I’d be incapable of writing good ORM code if I didn’t.
If you give a novice developer an OOP assignment, they’re likely to struggle with the learning curve, and likely to ultimately create something that has a lot of unnecessary complexity. If you give a novice developer MEAN assignment, they’ll create something that “works” a lot easier. But it’ll likely be full of bugs created by not understanding the underlying problems that the framework is simply not forcing you to address.
Which is what I think these “simple” frameworks do. Allow you to create working features without necessarily considering things like consistency and time complexity. I also think it’s why things like Mongo have been slowly adding more ACID consistency, and schema design features. Because people are coming to realize that those are problems that simply can’t go unaddressed. And why ORMs and things like GraphQL have become so popular, which to me look remarkably similar to the structured approach of OOP design patterns.
For me, ORMs become a problem when they're an excuse to avoid learning SQL. If you understand SQL, you will probably understand why the example you give is a bad idea. If you don't, you won't. I'm speaking from the point of view of having written, at this point, thousands of lines of SQL at minimum, and having decided that it's not how I primarily want to access data in the applications I write. The ORM queries I write are written with rough knowledge of the SQL they generate, and I try to be very careful with query builders to avoid the exact case you bring up.
I think LINQ in C# does a pretty good job of bridging this gap, actually. It could be better, but it discourages looping and encourages the declarative style that efficient SQL requires.