Orm: DDC-3376: Only one row is returned

[doctrinebot]

Jira issue originally created by user patryyyck:

Hi,

I don't know if you are the appropriate person.
I have an issue with Doctrine ORM or DBAL.
By the way, i'm not sure the issue is in one of these bundles.

I'm trying to get all the rows of on an entity A which is a SINGLE_TABLE with 3 discriminators.
This entity has a property "parent" which is a self referencing property (ManyToOne on entity A).

The getResult of my query only return the first row of my table.
When i replace getResult by getArrayResult, i have all my table.
But i want the objects ;-)

After hours of debugging, i saw that the Doctrine ORM ObjectHydrator.php loops on the result of my query (which have the good number of results):

while ($row = $this->*stmt->fetch(PDO::FETCH*ASSOC)) { $this->hydrateRowData($row, $cache, $result); }

But, the hydrateRowData executes another query to get the parent of each row (Doctrine DBAL Connection.php function executeQuery).
This function prepares a query to get the parent with a param (parent id) and i think the prepare function return the same statement (stmt) as the first query (the list of all the entity A).
So, the prepared query is executed but when the Doctrine ORM ObjectHydrator.php want to fetch the second row, he considers that there's no more rows to fetch and return the first rows.

I don't know if i am clear enough. Tell me if you want more details.

Oh. I forgot. If i try the same request with the same datas but in a mySQL database (pdo_mysql), i have a good result. I don't know if that helps.

Thanks

Regards

[doctrinebot]

Comment created by @ocramius:

The issue you described is very well known and it is a limitation of the ORM. What happens is following:

1. you have an inheritance A -> B | C | D (A being the root of the inheritance)
2. A has a self-referencing association to A (typically like a hierarchy of parent-child)
3. the ORM tries to load an A instance that has a reference to a parent of type A. In order to do so, the ORM has to resolve the actual type of the referenced parent, and therefore cannot lazily load it via a proxy (a query has to be executed in order to find out the value of the discriminator column)

This means that for performance reasons you should NEVER reference the root of an inheritance, but only leafs (where the ORM does not need to do this sort of resolution).

See also http://doctrine-orm.readthedocs.org/en/latest/reference/inheritance-mapping.html#id4 (Performance impact of inheritance mapping) for more details.

[doctrinebot]

Comment created by patryyyck:

Thanks Marco for your quick answer.

I understand the ORM limitation.
But i still don't understand why this limitation doesn't exist when i use a MySQL database.

[ghost]

Hi,

I just had the same problem.
patryyyck's explanation describes exactly the behaviour we have on our application.

It seems that instead of doing
while ($row = $this->_stmt->fetch(PDO::FETCH_ASSOC)) { $this->hydrateRowData($row, $cache, $result); }

if we do the following code to keep all rows in a array before hydrating, everything works perfectly.
$allRows = array(); while ($row = $this->_stmt->fetch(PDO::FETCH_ASSOC)) { $allRows[] = $row; } foreach ($allRows as $row) { $this->hydrateRowData($row, $cache, $result); }

Could that be a valid fix, or may i miss something ?

Thanks

Regards

[Ocramius]

@Greg-Git it could be an optimization, but you'd still be getting the same number of queries.

[amici]

I have the exact behavior, but with a specific twist - I do the same query, from the same page, with the 100% same code, but one is hosted at linux server and the other on windows server. Both are with Symfony 3 and they both speak to exactly the same instance of MS SQL.

The windows hosted version works perfectly fine, while the linux one returns just the first row - this is 100% reproducible. For further info, it's a simple DQL call, with one left join in it and 2 params set, using the getResults().

So, to me it doesn't seem like an ORM limitation. Maybe I actually have a different issue, but i believe the OP was speaking about the same thing.

[teadawg]

I had a similar issue like @amici. Turned out to be a problem with the driver. The official Microsoft driver solved it for me.

https://github.com/Microsoft/msphpsql/releases/tag/v4.3.0-RC1