Symfony - Doctrine - DQL - Create Array Object

Question

I did this query :

$language = $this->getDoctrine()->getManager()
->createQuery('SELECT c FROM AVCMediasBundle:Language c WHERE c.LangCode IS NOT NULL ORDER BY c.LanguageName')
->getResult();

This query returns to me this table :

id    langCode   languageName   countryName       ...
1       en          english       England          ...
2       en          english       United States     ...
3       en          english       Australia        ...
4       es          spanish       Spain            ...
5       es          spanish       Mexico           ...
6       es          spanish       Argentina        ...

With a {{ dump }} in my twig, I get this :

array:6 [
   0 => Langue {
    -id: 15
    -langCode: "en"
    -languageName: "English"
    -countryName: "England"
    }
    1 => Langue {
    -id: 1
    -langCode: "en"
    -languageName: "English"
    -countryName: "United States"}
    2 => Langue {
    -id: 3
    -langCode: "en"
    -languageName: "English"
    -countryName: "Australia"
    }
    3 => Langue {
    -id: 6
    -langCode: "es"
    -languageName: "Spanish"
    -countryName: "Spain"
    }
    4 => Langue {
    -id: 9
    -langCode: "es"
    -languageName: "Spanish"
    -countryName: "Mexico"
    }
    5 => Langue {
    -id: 2
    -langCode: "es"
    -languageName: "Spanish"
    -countryName: "Argentina"
    }
]

What I need now, is to group each entity where countryName have the same languageName How can I do an object array like that :

Array [
    0 => English
        Entity Langue
            => England
                -id: 15
                -langCode: "en"
                -languageName: "English"
                -countryName: "England"
            => United States
                -id: 1
                -langCode: "en"
                -languageName: "English"
                -countryName: "United States"}
            => Australia
                ...
    1 => Spanish
        Entity Langue
            => Spain
                -id: 6
                -langCode: "es"
                -languageName: "Spanish"
                -countryName: "Spain"
            => Mexico
                -id: 9
                -langCode: "es"
                -languageName: "Spanish"
                -countryName: "Mexico"
            => Australia
                ...
]

Is it possible with DQL ? or with some loops ? Thanks for your Help

Answer 1

It would be much better if you set up relationships between entities. That way Doctrine would take care of these kind of queries for you.

For instance, if you had two entities AVCMediasBundle:Language and AVCMediasBundle:Country you could stablish a relationship oneToMany between them (Language has many countries and a Country one Language) and access the countries with a language easily using the proper getter. Of course, this is just an example and your app and relations could be much more complex than the one presented. More info about relations here

If this isn't an option, a loop should do the trick:

$res = array();
foreach ($language as $item) {
    $keyLanguage = $item->getLanguageName();// Change the getter if it's not correct
    $keyCountry = $item->getCountryName();// Change the getter if it's not correct
    $res[$key][$keyCountry] = $item;
}

It would give you something like

array (
    "English" => array(
         "England" => object England,
         "United States" => object United States
    ...

It's not exactly in the form that you wanted (not really sure you can get that form), but I think it serves the same purpose.