Skip to content

Loading…

DDC-1960: mapping joins in native queries breaks if select columns are starting with columns from joined table #2633

Open
doctrinebot opened this Issue · 4 comments

3 participants

@doctrinebot

Jira issue originally created by user dready:

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 
------------<ins>---------</ins>-----------<ins>----------</ins>-------------
 u_id       | integer | not null  | plain    | 
 u_name     | text    | not null  | extended | 
 address_id | integer | not null  | plain    | 

*) address:

  Column  |  Type   | Modifiers | Storage  | Description 
----------<ins>---------</ins>-----------<ins>----------</ins>-------------
 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

@doctrinebot

Comment created by frederes:

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


$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(":catalogid", $catalogid)
;

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
[offercategoryid] => 14
)
)

As "oc" is a mapping, on the first loop the $parentAlias is not yet known and so :

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;
}

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

make it work...

Sorry for my dirty explanation...

@doctrinebot

Comment created by sustmi:

Here is a failing test for this issue:
sustmi@584163f

Release v2.5.0 is still affected.

I think there is a problem with ObjectHydrator::$resultPointers array.
What is the purpose of this array anyway?
Is it supposed to hold the data between multiple ObjectHydrator::hydrateRowData() calls?

Side note: This error occurs even with pure DQL queries (without NativeQueries and custom ResultSetMappings).

@doctrinebot

Comment created by sustmi:

Another simpler test case: sustmi@42a4da6

Also, I think this issue is a duplicate of http://www.doctrine-project.org/jira/browse/[DDC-2649](http://www.doctrine-project.org/jira/browse/DDC-2649) .

@beberlei beberlei was assigned by doctrinebot
@doctrinebot doctrinebot added the Bug label
@sustmi

I made a hotfix for this issue here: sustmi@de83b79
It needs some refactoring but I hope you get the idea.
(Array|Object)Hydrator needs $rowData['data'] to be in order from parent DQL alias to child DQL alias.
If the foreach ($rowData['data'] as $dqlAlias => $data) { loop processes child DQL alias before parent DQL, $this->_resultPointers does not contain pointer to parent's data from the current row but from the previous one.
See the failing test: sustmi@a6db93c#diff-9878dde71ae0e3ea89570bbdf876d29bR2041 .

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.