Querying and ORM are very different concepts. Object-relation mapping is concerned with, as it literally asserts, mapping between relations (or, more likely in practice, tables – but they are similar enough for the sake of this discussion) and objects. Maybe you are confusing ORM with the active record pattern (popularized by ActiveRecord, the library) which combines query building and ORM into some kind of unified concept? ActiveRecord, the library, confusingly called itself ORM when it was released which may be the source of that.
Was confused by that too (ORM and Active Records), but I spend some time learning about DDD which leads me into enterprise architecture and that's when I all the design pattern for interacting with data. Most web frameworks only have Query Builder and Active Records.
First, for definitions, I'd suggest we use wikipedia for ORM [1] and also Active Record Pattern [2].
I believe Active Record is a more specific implementation of something that is ORM-like. We can stop speaking of Active Record since my point holds for the more generic ORM, and therefore holds for Active Record as well.
To clarify my point, there is a fundamental impedance mismatch between object mapping of data vs relational database mapping of data. One implication of this is you cannot use database as a service. Interactions with database must instead be gated behind the ORM and the ORM controls the database interaction.
I'll note that database as a service is very powerful. For example, when there is an API contract exposing a value that is powered by some raw-dog SQL, when the database changes, anything using the API does not need to change. Only the SQL changes. In contrast, when an ORM exposes an object, an attribute might sometimes be loaded, sometimes not. A change to load or not load that attribute ripples through everything that uses that object. That type of change in ORM-land is the stuff of either N+1 problems, or Null-Pointers.
To back up a bit, let me re-iterate a bit about the impedance mismatch. Wikipedia speaks of this [1]: "By contrast, relational databases, such as SQL, group scalars into tuples, which are then enumerated in tables. Tuples and objects have some general similarity... They have many differences, though"
To drive the point home - in other words, you can't do everything in object world that you can do in a database 1:1. A consequence of this is that the ORM requires the application to view the database as a persistence store (AKA: data-store, AKA: object store, AKA: persistence layer). The ORM controls the interaction with database, you can't just use database as a data service.
I believe this point is illustrated most easily from queries.
To illustrate, let's pull some query code [3] from Java's Hibernate, a prototypical ORM.
```
public Movie getMovie(Long movieId) {
EntityManager em = getEntityManager();
Movie movie = em.find(Movie.class, new Long(movieId));
em.detach(movie);
return movie;
}
```
So, getting a release year might look like this:
```
int movieId = 123;
Movie m = orm.getMovie(movieId);
return m.getReleaseYear();
```
In contrast, if we put some raw-dogged SQL behind a method, we get this code:
Now, let's illustrate. To do this, let us look at the example of finding the release year of the highest grossing movie. As a service, that looks like this:
In contrast, as an ORM, you might have to load all Movies and then iterate. Maybe the ORM might have some magic sugar to get a 'min/max' value though. We can go on though, let's say we want to get the directors of the top 10 grossing movies. An ORM will almost certainly require you to load all movies and then iterate, or start creating some objects specifically to represent that data. In all cases, an ORM presents the contract is an an object rather than as an API call (AKA, a service).
For the update case, ORMs often do pretty well. ORMs can get into trouble with the impedance mismatch when doing things like trying to update joined entities. For example, "update all actors in movie X". Further, ORM (and objects) creates issues of stale/warm caches, nullity, mutability, performance, and more... What is worse, all of this is intrinsic, relational data and objects are intrinsically different.
> To illustrate, let's pull some query code [3] from Java's Hibernate, a prototypical ORM.
ORM and entity manager – which, in turn, is a query builder combined with a few other features. Your code is really focused on the latter. While the entity manager approach is not the same as active record, that is true, the bounds between query building and ORM, I think, are even clearer. In fact, your code makes that separation quite explicit. I can at least understand how ORM and query building get confused under active record.
> We can stop speaking of Active Record
While I agree in theory, since we are talking about ORM only, if we go by Wikipedia we cannot as is ends up confusing active record and ORM as being one and the same. That is a mistake. But as my teachers, and presumably yours too, told me in school: Don't trust everything you read on Wikipedia.
But we don't need to go to Wikipedia here anyway. Refreshingly, ORM literally tells what it is right in its name. All you need to do is spell it out: Object-Relation Mapping.
We might be talking past each other. I'm curious why you see such a strong distinction between ORM and active record. To that extent, do you have any references or links that explain Active Record as you understand it? I'm familiar with active record, but I don't think quite as much as you - I think I need to learn more. Do you have any good references I could look at?
My point is that (bluntly speaking), ORMs are intrinsically fucked because relational mapping and object mapping are just fundamentally different. Because of that difference, some things will always be difficult when doing so in any orm.
Here is an example of something that an ORM does well:
```
Person p = entityManger.findById(123);
p.setAge(23);
entityManager.persist(p);
```
I suppose an active record example is something like:
```
Person p = Person.findById(123);
p.setAge(23);
p.persist();
```
Regardless, of Active Record or ORM, the above is doing this query:
```
update person set age = 23 where id = 123;
```
The above is simple. When trying to update a linked entity is an example where ORMs are going to have complexity. Let's say a person owns books, and books are unique in the system. This type of query:
```
update book set person_id = (select id from person where name = 'Joe')
where person_id = (select id from person where name = 'Jill')
The ORM code is so convoluted... We do a full select for two Person entities, possibly eager fetching all their books with more queries (N+1) problem, but all other entities attached to a person as well, and then we do 'N' update statements. These types of problems are AFAIK unavoidable. They will happen for one scenario or another. One can choose the object representation to mitigate one case or another, but it's not long before something that is trivial in SQL becomes a huge burden in OO.
Thus, my thesis, Object-Relation Mapping will create unavoidable cases of convoluted code because RDBMS do not have a perfect mapping to Objects. The mapping is not perfect,the result of this is intrinsic complexity that is easily solved by sticking to SQL, but very difficult in ORMs (which manifest as various issues of inappropriate eager vs inappropriate late fetching, N+1 queries, caching issues, transaction issues, etc..)
