Order by a field in belongsToMany association

Question

I got deep associations and i wanted to know if it's possible to order result by a field contained in a BTM association:

$modeles = $this->Caracteristiques
        ->find()
        ->contain(['ModeleElements.ModeleOuvrages' => function ($q) {
            return $q
                ->where([
                    'ModeleOuvrages.couche_id' => 2,
                    'ModeleOuvrages.compte_client_id' => $this->Auth->user('compte_client_id')
                ]);
        }]);

Here is my query, i want a resultSet order by the field "nom" in "ModeleOuvrages" association.

Is it possible to get all "caracteristiques" order by "ModeleOuvrages.nom" ?

Edit: i did this:

$modeleOuvrages = $this->ModeleOuvrages
        ->find()
        ->where([
            'couche_id' => 2,
            'compte_client_id' => $this->Auth->user('compte_client_id')
        ])
        ->select([
            'ModeleOuvrages.nom',
            'ModeleElements.nom',
            'Caracteristiques.nom',
            'Caracteristiques.type'
        ])
        ->matching('ModeleElements.Caracteristiques')
        ->order(['ModeleOuvrages.nom', 'ModeleElements.nom', 'Caracteristiques.nom']);

and it works well. We can also do this with contain with more verbose.

Is the "where" clause sufficient to guarantee that there will be no more and no less than a single "ModeleOuvrage" for any given "Characteristique" found? If not, then I'm not sure how you would sort on that. If so, then I think what you want is to use "matching" instead of "contain", similar to the answer to a [question I asked](http://stackoverflow.com/questions/30718606/translating-query-involving-join-table-from-cakephp-1-3-to-3/30721698#30721698). — Greg Schmidt, Jun 24 '15 at 02:50

score 2 · Accepted Answer · answered Jun 28 '15 at 08:12

Yes, when using matching('Deep.Nested.Table') you will be able to sort on any column of that Table.

Remember that matching will create INNER JOINs and that it could possibly duplicate rows int the result. You can use $query->distinct(['MainTable.id']) to remove the duplicates.

Order by a field in belongsToMany association

1 Answers1