First, it's useful to define terms. When I talk about an ORM I talk about an ideal ORM which transparently maps ordinary object graph access patterns to relational database queries. These do exist, and they exhibit the OR mismatch problem I describe below. Some ORMs instead expose the OR mismatch and try to make the leaky abstraction a first class citizen. I would prefer not to call these ORMs, but it doesn't matter. Lastly, there are "ORMs" which are just query builders + DTOs, these are just not ORMs, but I think they're a great choice when interacting with SQL. You can accuse me of committing a no-true-Scotsman fallacy, and I can accuse you of moving the "ORM" goalposts.
Relational databases can represent graphs, and graphs naturally have relations, but in your OO language you can make choices about how to traverse an object graph based on external state, and such traversal is incremental and dynamic. Relational databases can have recursive queries, and these can be used to traverse graphs, but the shape of the query has to be known up front. Recursive queries can be dynamic over database state, but not over arbitrary external state. Even assuming some incredibly deeply integrated super-ORM, it's easy to imagine how programs that operate on graphs _and_ can be automatically mapped to an efficient set of relational queries are a limited subset.
This is the fundamental object-relational mismatch. You can use escape hatches, or you can contort your code, but every time you do this, you have to accept that you're no longer "mapping" in the transparent sense that ORMs were supposed to provide.
I think probably the easiest way to get an intuitive sense for the problem is to consider a simple object graph model:
User {
name
friends: List
posts: List
}
This is a mostly natural way of structuring this data. One natural (albeit contrived) operation might be:
user.friends[0].friends[0].posts
If you had a reason to do this operation, most people wouldn't think twice about it. There's overhead from the indirection, but nobody would think of this as an excruciatingly slow operation if working with native objects.
Now, how do you create an object that is backed by a relational database while still transparently letting you perform object-graph traversals such as the one above? It's easy to see how `User` would need to be an object with a `name` field. Since the data is recursive, you probably don't want to eagerly load all friends and posts, so you'd have proxy objects that make additional queries when you access them.
It's easy to see how this leads to the classic N+1 style issue. You have your user, you load their friends. Maybe your proxy object is smart enough to only load only their first friend. You end up making a bunch of additional queries after the first one to load the user. Especially when your database is on a disk and large, or accessed over the network, you can see how this quickly gets out of hand.
In the object/graph model, the relationships are _internalised_. They're represented _within_ the object. But in the relational model, relationships are external. To "map" from one to the other efficiently, you can't just represent things as objects with some glue, because you keep running into these "look ahead" issues. When you access user.friends or even user.friends[0], your mapper has no way to know what you're going to ask for next.
Of course, one way to solve this would be with deeper integration or a DSL. Let's say you had a query language which can represent the above query, and then you analyse this query to try to map it efficiently to a relational query. Sounds like we've solved the problem? Well kind of, yes. Except we're no longer mapping the object model to the relational model. A given query leaves you with dead objects, you've just delayed the problem while leaking abstractions. You can add proxies to those but you're now back to square one except you've maybe improved performance a little bit.