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

          People

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

            Dates

            • Created:
              Updated: