[DDC-1960] mapping joins in native queries breaks if select columns are starting with columns from joined table Created: 31/Jul/12  Updated: 21/Nov/12

Status: Open
Project: Doctrine 2 - ORM
Component/s: ORM
Affects Version/s: 2.1.4, 2.1.7
Fix Version/s: None
Security Level: All

Type: Bug Priority: Major
Reporter: Thomas Subera Assignee: Benjamin Eberlei
Resolution: Unresolved Votes: 0
Labels: None
Environment:

ubuntu kernel 2.6.32-40-server
php 5.3.10-1ubuntu2ppa6~lucid with Suhosin-Patch (cli)
apache 2 2.2.14-5ubuntu8.9
postgres 9.1.4-1~lucid4


Attachments: Zip Archive testcase.zip    

 Description   

Using a simple Testcase like in http://docs.doctrine-project.org/projects/doctrine-orm/en/2.1/reference/native-sql.html there are two Tables:

*) users:

   Column   |  Type   | Modifiers | Storage  | Description 
------------+---------+-----------+----------+-------------
 u_id       | integer | not null  | plain    | 
 u_name     | text    | not null  | extended | 
 address_id | integer | not null  | plain    | 

*) address:

  Column  |  Type   | Modifiers | Storage  | Description 
----------+---------+-----------+----------+-------------
 a_id     | integer | not null  | plain    | 
 a_street | text    | not null  | extended | 
 a_city   | text    | not null  | extended | 

address_id is a foreign key to address;

Now i created the Entities and setup a native query using ResultSetMappingBuilder:

$rsm = new \Doctrine\ORM\Query\ResultSetMappingBuilder($entityManager);
$rsm->addRootEntityFromClassMetadata('MyProject\Entity\Users', 'u');
$rsm->addJoinedEntityFromClassMetadata('MyProject\Entity\Address', 'a', 'u', 'address');

$query = '
    SELECT
        u.*,
        a.*
    FROM
        users u
    LEFT JOIN address a ON (u.address_id = a.a_id)
';

/** @var $native \Doctrine\ORM\NativeQuery */
$native = $entityManager->createNativeQuery($query, $rsm);

$ret = $native->getResult();

This returns the Entities correctly:

array(2) {
  [0] =>
  class MyProject\Entity\Users#61 (3) {
    protected $id =>
    int(1)
    protected $name =>
    string(5) "Smith"
    protected $address =>
    class MyProject\Entity\Address#63 (4) {
      protected $id =>
      int(1)
      protected $street =>
      string(8) "Broadway"
      protected $city =>
      string(8) "New York"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#64 (9) {
        ...
      }
    }
  }
  [1] =>
  class MyProject\Entity\Users#66 (3) {
    protected $id =>
    int(2)
    protected $name =>
    string(7) "Sherlok"
    protected $address =>
    class MyProject\Entity\Address#67 (4) {
      protected $id =>
      int(2)
      protected $street =>
      string(13) "Oxford Street"
      protected $city =>
      string(6) "London"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#68 (9) {
        ...
      }
    }
  }
}

BUT if you change the order of the select columns starting with ones from address you get borked Data:

$query = '
    SELECT
        a.*,
        u.*
    FROM
        users u
    LEFT JOIN address a ON (u.address_id = a.a_id)
';
array(2) {
  [0] =>
  class MyProject\Entity\Users#61 (3) {
    protected $id =>
    int(1)
    protected $name =>
    string(5) "Smith"
    protected $address =>
    class MyProject\Entity\Address#63 (4) {
      protected $id =>
      int(2)
      protected $street =>
      string(13) "Oxford Street"
      protected $city =>
      string(6) "London"
      protected $users =>
      class Doctrine\ORM\PersistentCollection#64 (9) {
        ...
      }
    }
  }
  [1] =>
  class MyProject\Entity\Users#66 (3) {
    protected $id =>
    int(2)
    protected $name =>
    string(7) "Sherlok"
    protected $address =>
    NULL
  }
}

This happens because the function Doctrine\ORM\Internal\Hydration\AbstractHydrator::_gatherRowData does not consider the Mapping i set up. Instead it just add the columns as they get starting with address ones.

Doctrine\ORM\Internal\Hydration\ObjectHydrator::_hydrateRow then knows the Mapping and ignores the first Address as there is no User to map on, cycling to the next row will then add the address of the second row to the user from the first one.

There are multiple ways to fix this. One would be to consider the mapping in _gatherRowData, the second to rewrite the _hydrateRow generating the Entities first and then the mapping in a second foreach loop.

This bugger had me for 2 days until i finally figured it out.

thanks



 Comments   
Comment by Frederic [ 21/Nov/12 ]

Hello,

Has same issue with using DQL /createQuery() ! Try all the day to find where was my mistake but seems to be a CRITICAL bug !
How did you solve this ?

Doctrine version used : 2.3.1-DEV

<code>
$query = $this->getEntityManager()->createQuery("
SELECT cc, oc
FROM category cc
JOIN cc.offer_category oc
WHERE cc.catalog = :catalog_id
ORDER BY oc.name ASC
")
->setParameter(":catalog_id", $catalog_id)
;

</code>

Problem is that the order of the Aliases (cc, oc) is not considered on building SQL .
In my case, in the ObjectHydrator::hydrateRowData method :

$rowData = $this->gatherRowData($row, $cache, $id, $nonemptyComponents);

returns

Array
(

[oc] => Array
(
[id] => 14
[name] => toto
)
[cc] => Array
(
[catalog_id] => 1
[offer_category_id] => 14
)
)

As "oc" is a mapping, on the first loop the $parentAlias is not yet known and so :
<code>
if ($this->_rsm->isMixed && isset($this->_rootAliases[$parentAlias]))

{ echo "parentObject 1\n"; $first = reset($this->_resultPointers); $parentObject = $first[key($first)]; }

else if (isset($this->_resultPointers[$parentAlias]))

{ echo $parentAlias." parentObject 2\n"; $parentObject = $this->_resultPointers[$parentAlias]; }

else

{ // HERE : on first loop, for "oc", parent not yet known so skipped !!! continue; }

</code>

using a workaround on ObjectHydrator::hydrateRowData like this :
$rowData = array_reverse($rowData);

make it work...

Sorry for my dirty explanation...

Generated at Sat Oct 25 16:45:31 UTC 2014 using JIRA 6.2.3#6260-sha1:63ef1d6dac3f4f4d7db4c1effd405ba38ccdc558.