ResultSetMappingBuilder creates a life of it's own and starts querying the database without being asked to, resulting in errors

[ghost]

I have a very simple code:

    $rsm = new ResultSetMappingBuilder($this->_em);
    $rsm->addRootEntityFromClassMetadata('App\Entity\Order', 'o');
    $query = $this->_em->createNativeQuery('CALL usp_order_s_orders()', $rsm);
    $orders = $query->getResult();

This code works at first. It calls the procedure and populates an array of Order entities.

However, if I add a one-to-one relation between the table Order and another table, say, OrderData, then the above code start QUERYING THE ORDERDATA TABLE.

Now, first of all this should NEVER happen. All queries going into and from the database in my application are controller via procedures, and are appropriately logged. No "SELECT" query should be emitted by Doctrine without me knowing about it. It is doing that under the hood, this behavior is completely undocumented, and this is a very, very bad security and design issue and makes me very worried about using Doctrine at all.

Second, this insane behavior also leads to a bug. The select query looks something like this

"SELECT t0.everyfieldintheworld WHERE t0.order_id = ? [1] FROM order_data"

Leading to this error message:

request.CRITICAL: Uncaught PHP Exception Doctrine\DBAL\Exception\DriverException: "An exception occurred while executing Giant Query: SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute." at path/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 128 {"exception":"[object] (Doctrine\DBAL\Exception\DriverException(code: 0): An exception occurred while executing Giant Query:\n\nSQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. at path/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php:128)\n[previous exception] [object] (Doctrine\DBAL\Driver\PDO\Exception(code: HY000): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. at path/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDO/Exception.php:18)\n[previous exception] [object] (PDOException(code: HY000): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute. at path/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php:112)"} []

[lcobucci]

@davidmoseler the behaviour is documented in https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/reference/faq.html#why-is-an-extra-sql-query-executed-every-time-i-fetch-an-entity-with-a-one-to-one-relation

(As far as I remember - it's been a while) this extra query will be executed during hydration when the result set and the result set map don't reference the associated entity. Try updating the query (ran by the procedure) to perform a join and add the joined entity to the RSM, that should be enough to solve your issue.

I agree that the docs could detail this better - it's OSS, though, so you may also help us with improving things.

More details on eager loading: https://www.doctrine-project.org/projects/doctrine-orm/en/2.9/reference/working-with-objects.html#by-eager-loading

It's also good to highlight: this behaviour assumes that the project uses buffered queries (which is PHP's defaults).

[lcobucci]

@derrabus @greg0ire this looks like a good candidate for a Q&A discussion instead of an issue 😁

[derrabus]

Moved. 🙂

[ghost]

Hi @lcobucci ! Thank you very much for the clarifying response.

With the clarification, I would say there are two "issues" at hand, neither of which is a github-style issue, it's true. One is a documentation issue: that behavior should probably be documented or at least the FAQ Q/A linked in the page about the ResultSetMappingBuilder. The FAQ is not the proper place to document such a fundamental behavior of a class. I would never think about looking in a FAQ for a class behavior documentation!

The other would be a feature request, if the maintainers agree that it makes sense. I don't want to join the child entities for every query. Think about the child (one-to-one mapped) entity as a non-required field. Now the problem is that ResultSetMappingBuilder acts as an inner join, so that, if the child is not present or, worse, if I just don't want to fetch the child for this particular query, the result set is empty or an additional query is done (resulting in a mysql error) depending on the value of the second parameter to the builder. This is crazy.

I almost gave up from Doctrine yesterday because of this, and the only way around that I found was to write my own "result set mapping" utility.

It feels that the ResultSetMapping functionality should definitely be more flexible, especially because it is the only Doctrine Entity interface to raw SQL. If people are writing raw SQL, it is exactly because they need the extra flexibility. Now if Doctrine does not properly handle OUTER JOIN results or doesn't allow to leave the JOIN out altogether, it is really a shame and defies the purpose of having a raw SQL wrapper altogether. Not every associated entity is something that you want to fetch at every query! Not to mention that altering the mysql procedure might not be an option!

[stof]

Well, this is not something affecting only ResultSetMapping with a OneToOne. It is true for any hydration of the inverse side of a OneToOne. The solution to avoid such queries is to not using bidirectional OneToOne relations in your mapping (most of the time, you can make things work with a unidirectional relation instead).

[lcobucci]

One is a documentation issue: that behavior should probably be documented or at least the FAQ Q/A linked in the page about the ResultSetMappingBuilder. The FAQ is not the proper place to document such a fundamental behavior of a class. I would never think about looking in a FAQ for a class behavior documentation!

Ack! You can send a pull request to suggest how it would be best documented. That would be the best course of action since it presents a concrete solution for the problem - instead of only raising yet another problem.

Writing good and useful documentation it's not an easy and obvious thing for many people - I'm terrible at it, honestly. So, please try to be keep that in mind.

The other would be a feature request, if the maintainers agree that it makes sense. I don't want to join the child entities for every query. Think about the child (one-to-one mapped) entity as a non-required field. Now the problem is that ResultSetMappingBuilder acts as an inner join, so that, if the child is not present or, worse, if I just don't want to fetch the child for this particular query, the result set is empty or an additional query is done (resulting in a mysql error) depending on the value of the second parameter to the builder. This is crazy.

I almost gave up from Doctrine yesterday because of this, and the only way around that I found was to write my own "result set mapping" utility.

It feels that the ResultSetMapping functionality should definitely be more flexible, especially because it is the only Doctrine Entity interface to raw SQL. If people are writing raw SQL, it is exactly because they need the extra flexibility. Now if Doctrine does not properly handle OUTER JOIN results or doesn't allow to leave the JOIN out altogether, it is really a shame and defies the purpose of having a raw SQL wrapper altogether. Not every associated entity is something that you want to fetch at every query! Not to mention that altering the mysql procedure might not be an option!

What we need to consider here is data consistency and the fact of Doctrine ORM is a generic tool that doesn't know how objects will be used.

Imagine that you have an operation on the owning side of the 1:1 association that does one thing or another based on the existence of the associated object. If the ORM doesn't load the associated object (as the flexibility above would allow), that operation would behave incorrectly - potentially causing a lot of harm on the application.

This kind of behaviour is quite difficult to debug as it introduces variable consistence based on the origin of the object.

Your use case is focused on retrieving data and I'd say that you probably don't need to hydrate objects if you don't care about the consistency boundary introduced by your class. So, DBAL would be more than enough for that.

[ghost]

@lcobucci I'm surely stepping outside my comfort zone and talking about something that you understand a lot better, as a doctrine maintainer. However, naively I would think the best design would be for the user to be able to disable this silent automatic loading of associated objects, and, if any code subsequently tries to access the associated object property, an exception is thrown. This would presumably be relatively simple to accomplish with setters and getters.

The reason is because, as you said, an ORM is a generic tool that doesn't know how objects will be used. So it is a little bit unsettling to know that it is opinionated to the point where something cannot be accomplished at all.

I think it would be interesting to have some sort of "view" of Entities where only some of the properties are loaded and accessible. Then a ResultSetMapping could map into this partial view instead of mapping the result into an Entity. At least for my use case, it is pretty common to have a big chunk of data broken into several tables and only parts of the data being loaded for a particular purpose. So the concept of partial views to a table is something that I can accomplish quite fine with SQL but can't with Doctrine, which is a bummer.

Like the one @stof suggested, there are some workarounds, but no solution is perfect from a modelling standpoint except one that would allow partial views of tables. As things are right now, I'm using Doctrine exclusively for the purpose of managing migrations.