Strange doctrine results array structure if I pass in DQL query three times join with the same related entity

Question

I get a strange result array structure

this is my DQL query and php code on symfony platform:

    $params = array();

    $query = $this->getEntityManager()
        ->createQuery('
            SELECT c1 as cat1, c2 as cat2, c3 as cat3, count(s.id) as tot
            FROM Priatdru\PolyglotBundle\Entity\SearchQuery s
            INNER JOIN ApplicationSonataClassificationBundle:Category c1 WITH s.category1 = c1.id
            INNER JOIN ApplicationSonataClassificationBundle:Category c2 WITH s.category2 = c2.id
            LEFT  JOIN ApplicationSonataClassificationBundle:Category c3 WITH s.category3 = c3.id
            WHERE s.sessionId != :session_id
            AND (
              (s.category1 = :category1 AND s.category2 IS NOT NULL)
              OR s.category2 = :category1
              OR s.category3 = :category1
              )
            GROUP BY cat1,cat2,cat3
            ORDER BY tot ASC
        ');

    $params['session_id'] = $session_id;
    $params['category1'] = $category1;
    $query->setParameters($params);

   return $query->getResult();

var_dump:

  array (size=10)
    0 => 
      array (size=1)
        'cat1' => 
          object(Application\Sonata\ClassificationBundle\Entity\Category)[1309]
            protected 'id' => int 8755
                ...
            private 'nameEn' => string 'French' (length=6)
            private 'nameFr' => string 'Français' (length=9)
            private 'nameIt' => string 'Francese' (length=8)
            private 'nameDe' => string 'Französisch' (length=12)
            private 'nameEs' => string 'Francés' (length=8)
            private 'slugEn' => string 'french' (length=6)
            private 'slugFr' => string 'francais' (length=8)
            private 'slugIt' => string 'francese' (length=8)
            private 'slugDe' => string 'franzosisch' (length=11)
            private 'slugEs' => string 'frances' (length=7)
            protected 'name' => string 'fr' (length=2)
            protected 'slug' => string 'french' (length=6)
            protected 'enabled' => boolean true
            protected 'description' => string 'French' (length=6)
            protected 'createdAt' => 
              object(DateTime)[1306]
                ...
            protected 'updatedAt' => 
              object(DateTime)[1307]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[1464]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    1 => 
      array (size=1)
        'cat2' => 
          object(Application\Sonata\ClassificationBundle\Entity\Category)[2390]
            protected 'id' => int 8821
                ...
            private 'nameEn' => string 'Italian' (length=7)
            private 'nameFr' => string 'Italien' (length=7)
            private 'nameIt' => string 'Italiano' (length=8)
            private 'nameDe' => string 'Italienisch' (length=11)
            private 'nameEs' => string 'Italiano' (length=8)
            private 'slugEn' => string 'italian' (length=7)
            private 'slugFr' => string 'italien' (length=7)
            private 'slugIt' => string 'italiano' (length=8)
            private 'slugDe' => string 'italienisch' (length=11)
            private 'slugEs' => string 'italiano' (length=8)
            protected 'name' => string 'it' (length=2)
            protected 'slug' => string 'italian' (length=7)
            protected 'enabled' => boolean true
            protected 'description' => string 'Italian' (length=7)
            protected 'createdAt' => 
              object(DateTime)[2312]
                ...
            protected 'updatedAt' => 
              object(DateTime)[2396]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[2376]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    2 => 
      array (size=2)
        'cat3' => null
        'tot' => string '2' (length=1)
    3 => 
      array (size=1)
        'cat1' => 
          object(Application\Sonata\ClassificationBundle\Entity\Category)[2390]
            protected 'id' => int 8821
                ...
            private 'nameEn' => string 'Italian' (length=7)
            private 'nameFr' => string 'Italien' (length=7)
            private 'nameIt' => string 'Italiano' (length=8)
            private 'nameDe' => string 'Italienisch' (length=11)
            private 'nameEs' => string 'Italiano' (length=8)
            private 'slugEn' => string 'italian' (length=7)
            private 'slugFr' => string 'italien' (length=7)
            private 'slugIt' => string 'italiano' (length=8)
            private 'slugDe' => string 'italienisch' (length=11)
            private 'slugEs' => string 'italiano' (length=8)
            protected 'name' => string 'it' (length=2)
            protected 'slug' => string 'italian' (length=7)
            protected 'enabled' => boolean true
            protected 'description' => string 'Italian' (length=7)
            protected 'createdAt' => 
              object(DateTime)[2312]
                ...
            protected 'updatedAt' => 
              object(DateTime)[2396]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[2376]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    4 => 
      array (size=1)
        'cat2' => 
          object(Application\Sonata\ClassificationBundle\Entity\Category)[1309]
            protected 'id' => int 8755
                ...
            private 'nameEn' => string 'French' (length=6)
            private 'nameFr' => string 'Français' (length=9)
            private 'nameIt' => string 'Francese' (length=8)
            private 'nameDe' => string 'Französisch' (length=12)
            private 'nameEs' => string 'Francés' (length=8)
            private 'slugEn' => string 'french' (length=6)
            private 'slugFr' => string 'francais' (length=8)
            private 'slugIt' => string 'francese' (length=8)
            private 'slugDe' => string 'franzosisch' (length=11)
            private 'slugEs' => string 'frances' (length=7)
            protected 'name' => string 'fr' (length=2)
            protected 'slug' => string 'french' (length=6)
            protected 'enabled' => boolean true
            protected 'description' => string 'French' (length=6)
            protected 'createdAt' => 
              object(DateTime)[1306]
                ...
            protected 'updatedAt' => 
              object(DateTime)[1307]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[1464]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    5 => 
      array (size=2)
        'cat3' => null
        'tot' => string '5' (length=1)
    6 => 
      array (size=1)
        'cat2' => 
          object(Application\Sonata\ClassificationBundle\Entity\Category)[2397]
            protected 'id' => int 8736
                ...
            private 'nameEn' => string 'Spanish' (length=7)
            private 'nameFr' => string 'Espagnol' (length=8)
            private 'nameIt' => string 'Spagnolo' (length=8)
            private 'nameDe' => string 'Spanisch' (length=8)
            private 'nameEs' => string 'Español' (length=8)
            private 'slugEn' => string 'spanish' (length=7)
            private 'slugFr' => string 'espagnol' (length=8)
            private 'slugIt' => string 'spagnolo' (length=8)
            private 'slugDe' => string 'spanisch' (length=8)
            private 'slugEs' => string 'espanol' (length=7)
            protected 'name' => string 'es' (length=2)
            protected 'slug' => string 'spanish' (length=7)
            protected 'enabled' => boolean true
            protected 'description' => string 'Spanish' (length=7)
            protected 'createdAt' => 
              object(DateTime)[2335]
                ...
            protected 'updatedAt' => 
              object(DateTime)[2330]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[2370]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    7 => 
      array (size=2)
        'cat3' => null
        'tot' => string '5' (length=1)
    8 => 
      array (size=1)
        'cat1' => 
          object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1671]
            public '__initializer__' => null
            public '__cloner__' => null
            public '__isInitialized__' => boolean true
            protected 'id' => int 8616
                ...
            private 'nameEn' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'English' (length=7)
            private 'nameFr' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'Anglais' (length=7)
            private 'nameIt' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'Inglese' (length=7)
            private 'nameDe' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'Englisch' (length=8)
            private 'nameEs' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'Inglés' (length=7)
            private 'slugEn' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'english' (length=7)
            private 'slugFr' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'anglais' (length=7)
            private 'slugIt' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'inglese' (length=7)
            private 'slugDe' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'englisch' (length=8)
            private 'slugEs' (Application\Sonata\ClassificationBundle\Entity\Category) => string 'ingles' (length=6)
            protected 'name' => string 'en' (length=2)
            protected 'slug' => string 'english' (length=7)
            protected 'enabled' => boolean true
            protected 'description' => string 'English' (length=7)
            protected 'createdAt' => 
              object(DateTime)[2394]
                ...
            protected 'updatedAt' => 
              object(DateTime)[2399]
                ...
            protected 'position' => null
            protected 'children' => 
              object(Doctrine\ORM\PersistentCollection)[2326]
                ...
            protected 'parent' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Category)[1540]
                ...
            protected 'media' => null
            protected 'context' => 
              object(Proxies\__CG__\Application\Sonata\ClassificationBundle\Entity\Context)[1518]
                ...
    9 => 
      array (size=2)
        'cat3' => null
        'tot' => string '17' (length=2)

When I change the select line in the DQL query :

  SELECT c1.name as cat1, c2.name as cat2, c3.name as cat3, count(s.id) as tot

now structure array is what I EXPECT : var_dump:

  array (size=4)
      0 => 
        array (size=4)
          'cat1' => string 'fr' (length=2)
          'cat2' => string 'it' (length=2)
          'cat3' => null
          'tot' => string '2' (length=1)
      1 => 
        array (size=4)
          'cat1' => string 'fr' (length=2)
          'cat2' => string 'es' (length=2)
          'cat3' => null
          'tot' => string '5' (length=1)
      2 => 
        array (size=4)
          'cat1' => string 'it' (length=2)
          'cat2' => string 'fr' (length=2)
          'cat3' => null
          'tot' => string '5' (length=1)
      3 => 
        array (size=4)
          'cat1' => string 'en' (length=2)
          'cat2' => string 'fr' (length=2)
          'cat3' => null
          'tot' => string '17' (length=2)

If someone had already similar problems please lets share informations. Thanks all community in advance! regards

Answer 1

I hope i can help you clearing out a couple of thing.

In the first example your DQL query it's working with the entity:

SELECT c1 as cat1, c2 as cat2, c3 as cat3

as you can see with c1 as cat1 the computed result-set will be of ApplicationSonataClassificationBundle:Category type and, of course, all other joined relation will be presented aswell. For all intent and purpose you are dealing with objects here.

in the second example you provided:

SELECT c1.name as cat1, c2.name as cat2, c3.name as cat3, count(s.id) as tot

as you can see you are working with the entity field (c1.name), forcing an ArrayHydration on the result.

---

If you want to keep your first approach make sure to impose a proper hydration:

$query = $em->createQuery('SELECT u FROM CmsUser u');
$users = $query->getResult(Query::HYDRATE_ARRAY);

for further reference check this page.

---

EDIT:

As in this case you're doing a very specific extraction it's common practice to drop the object relation altogether and move to an array approach. You can make use of a more specific select 1.name as you stated in the second example;

should the need arise to retrive the object you may consider adding the identifier to the extraction c1.id and and lazly fetch-it from a repository (findBy).

hope it helps,

regards.