CODEWITHSUNDEEP
cakephpcakephp-3.0query-builder
Alimon Karim
Alimon Karim

Reputation: 4469

Cakephp 3 : How to add order in queryBuilder for associative data?

Here I have a events field which have a realtion with passwords.I need to order all passwords by download_count DESC. So I have tried below code. It's not giving me any error but desc is not working. 

 $event = $this->Events->get($id, [
                  'contain' => [
                      'EventPasswordAll.Passwords' => [
                          'queryBuilder' => function (Query $q) {
                              return $q->order(['download_count' => 'DESC']);
                          }
                      ]
                  ]
             ]);

I have also tried 

 return $q->order(['Passwords.download_count' => 'DESC']);

I get the query 

SELECT EventPasswordAll.id AS `EventPasswordAll__id`, EventPasswordAll.event_id AS `EventPasswordAll__event_id`, EventPasswordAll.password_id AS `EventPasswordAll__password_id`, Passwords.id AS `Passwords__id`, Passwords.name AS `Passwords__name`, Passwords.collection_id AS `Passwords__collection_id`, Passwords.photo_count AS `Passwords__photo_count`, Passwords.download_count AS `Passwords__download_count`, Passwords.created AS `Passwords__created`, Passwords.expires_at AS `Passwords__expires_at`, Passwords.recommended AS `Passwords__recommended`, Passwords.accepted AS `Passwords__accepted`, Passwords.user_id AS `Passwords__user_id` FROM event_password_all EventPasswordAll INNER JOIN passwords Passwords ON Passwords.id = (EventPasswordAll.password_id) WHERE EventPasswordAll.event_id in (14)

How to add order in queryBuilder for associative data ?

Upvotes: 3

Views: 4674

Answers (1)

ndm
ndm

Reputation: 60463

With an association of EventPasswordAll belongsTo Passwords, the passwords table record will be retrieved via a join, as can be seen in the query. In this case there is no query for Passwords, and thus the query builder is not being invoked.

You have to set the order for the EventPasswordAll query builder instead, like

$event = $this->Events->get($id, [
    'contain' => [
        'EventPasswordAll' => [
            'queryBuilder' => function (Query $q) {
                return $q->order(['Passwords.download_count' => 'DESC']);
            },
            'Passwords'
        ]
    ]
]);

Upvotes: 1

Related Questions