php - 奇怪的学说结果数组结构如果我在 DQL 查询中传递三次与相同的相关实体连接

Question

我得到一个奇怪的结果数组结构

这是我在 symfony 平台上的 DQL 查询和 php 代码:

    $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();

变量转储:

  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)

当我更改 DQL 查询中的选择行时:

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

现在结构数组是我所期望的:变量转储:

  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)

如果有人遇到过类似问题，请分享信息。提前感谢所有社区!问候

Answer 1

我希望我能帮你解决一些问题。

在第一个示例中，您的 DQL 查询与实体一起工作:

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

正如您在 c1 as cat1 中看到的那样，计算的结果集将是 ApplicationSonataClassificationBundle:Category 类型，当然，所有其他连接关系也将出现。出于所有意图和目的，您在这里处理对象。

在您提供的第二个示例中:

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

如您所见，您正在使用实体字段 (c1.name)，对结果强制执行 ArrayHydration。

---

如果您想保持第一种方法，请确保适当补水:

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

进一步引用检查this page.

---

编辑:

在这种情况下，您正在进行非常具体的提取，通常的做法是完全放弃对象关系并转向数组方法。您可以使用第二个示例中所述的更具体的 select 1.name；

如果需要检索对象，您可以考虑将标识符添加到提取 c1.id 并lazly 从存储库 (findBy) 中获取它。

希望对你有帮助

问候。