Strange structure of doctrine results array, if I pass the DQL query three times, join the same linked object
I am getting strange structure of results array
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 string in a DQL query:
SELECT c1.name as cat1, c2.name as cat2, c3.name as cat3, count(s.id) as tot
Now the array of the structure 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 anyone has had similar issues, please report it. Thanks to all the community in advance! considers
+3
source to share
1 answer
Hope I can help you clear a couple of things.
In the first example, your DQL query works with an 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 ApplicationSonataClassificationBundle: Category type and of course all other merged relationships will be represented as well. For all intents and purposes, you are dealing with objects here.
in the second example you gave:
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 the ArrayHydration to result.
If you want to keep your first set, be sure to apply proper hydration:
$query = $em->createQuery('SELECT u FROM CmsUser u');
$users = $query->getResult(Query::HYDRATE_ARRAY);
for additional verification: this page.
EDIT:
As in this case, you are doing a very specific extraction so that it is common practice to drop the relation to the object altogether and go for an array approach. You can use the more specific 1.name as you indicated in the second example;
if there is a need to return an object you can add when adding an id to fetch c1.id as well as lazly fetch-it from the repository (findBy).
hope this helps,
Sincerely.
+1
source to share