Doctrine 2 - ORM
  1. Doctrine 2 - ORM
  2. DDC-1960

mapping joins in native queries breaks if select columns are starting with columns from joined table

    Details

    • Type: Bug Bug
    • Status: Open
    • Priority: Major Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1.4, 2.1.7
    • Fix Version/s: None
    • Component/s: ORM
    • Security Level: All
    • 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

      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

        Activity

        Hide
        Frederic added a comment - - edited

        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...

        Show
        Frederic added a comment - - edited 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...

          People

          • Assignee:
            Benjamin Eberlei
            Reporter:
            Thomas Subera
          • Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

            • Created:
              Updated: