CakePHP3: group by associated
<p>Is it possible to group a result set by associated?</p>

<p>I have the following associations</p>

<p><strong>AgthemesTable</strong></p>

<pre><code>$this-&gt;belongsTo('Sites');
$this-&gt;hasMany('Agthemelanguages');
</code></pre>

<p><strong>AgthemeLanguagesTable</strong></p>

<pre><code>$this-&gt;belongsTo('Languages');
</code></pre>

<p>As I want to get all <code>Agthemes</code> for each <code>Agthemelanguages</code>, I wrote the following query:</p>

<pre><code>$agThemes = $this-&gt;Agthemes-&gt;find('all')
-&gt;select(['id', 'name', 'approved', 'published'])
    -&gt;contain ([
            'Sites' =&gt; function ($q) {
                return $q-&gt;select(['id'])
                -&gt;autoFields(false);
            },
            'Agthemelanguages.Languages' =&gt; function ($q) use ($localized_name) {
                return $q-&gt;select(
                        [
                                'id',
                                'code',
                                'code3',
                                $localized_name
                        ]
                        );
            }
        ]
    )
    -&gt;where([
                'approved' =&gt; true,
                'published' =&gt; true
            ])
    -&gt;matching(
            'Sites', function ($q) use($site_id) {
                return $q-&gt;where([
                    'Sites.id' =&gt; $site_id
                ]);
        }
    )
    -&gt;group('language_id')
    -&gt;all();
</code></pre>

<p>But I get the error:</p>

<pre><code>Error: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'language_id' in 'group statement' 
</code></pre>

<p>Effectively, there is no <code>AgthemeLanguages</code> in the generated sql query:</p>

<pre><code>SELECT Agthemes.id AS `Agthemes__id`, Agthemes.name AS `Agthemes__name`, Agthemes.approved AS `Agthemes__approved`, Agthemes.published AS `Agthemes__published` FROM agthemes Agthemes INNER JOIN sites Sites ON (Sites.id = :c0 AND Sites.id = (Agthemes.site_id)) WHERE (approved = :c1 AND published = :c2) GROUP BY language_id 
</code></pre>

<p>Without <code>group</code> statement, I get suc kind of result:</p>

<pre><code>'items' =&gt; [
    (int) 0 =&gt; object(App\Model\Entity\Agtheme) {

        'id' =&gt; (int) 40,
        'name' =&gt; '...',
        'approved' =&gt; (int) 1,
        'published' =&gt; (int) 1,
        'agthemelanguages' =&gt; [
            (int) 0 =&gt; object(App\Model\Entity\Agthemelanguage) {

                'id' =&gt; (int) 372,
                'agtheme_id' =&gt; (int) 40,
                'language_id' =&gt; (int) 1,
                'name' =&gt; '....',
                'comment' =&gt; '....',
                'language' =&gt; object(App\Model\Entity\Language) {

                    'id' =&gt; (int) 1,
                    ...
                },
                ...,
           ],
           ...,
        },
        ...
    ]
</code></pre>

<p>How to define such query?
Al</p>

<p><strong>EDIT</strong></p>

<p>I finally got what I expect writing the query like that but it needs to make another query first to know all available <code>Agthemes</code> and put their ids in a <code>$agThemeIds</code> array:</p>

<pre><code>$agThemeLanguages = $this-&gt;Languages-&gt;find('all')
-&gt;select(
                        [
                                'id',
                                'code',
                                'code3',
                                $localized_name
                        ]
                        )
    -&gt;contain ([
            'Agthemelanguages' =&gt; function ($q) use ($agThemeIds) {
                        return $q-&gt;where(['agtheme_id IN ' =&gt; $agThemeIds]);
                    }
        ]
    )
    -&gt;matching('Agthemelanguages', function ($q) use ($agThemeIds) {
        return $q-&gt;where(['agtheme_id IN ' =&gt; $agThemeIds]);
    })
    -&gt;group(['Languages.id'])                           
    -&gt;all();
</code></pre>