CakePHP 2.x Order By desc based on HasMany table records

Question

I have Users and Followers table. Followers have HasMany relation with Users i.e. any user can have multiple followers.

Array
(
    [0] => Array
        (
            [User] => Array
                (
                    [id] => 26
                    [seller_id_number] => 
                    [facebook_id] => 
                    [twitter_id] => 

                )



            [Followers] => Array
                (
                    [0] => Array
                        (
                            [id] => 6
                            [user_id] => 2
                            [follower_id] => 26
                            [date_added] => 2017-09-22 03:58:21
                            [date_updated] => 2017-09-22 07:58:21

                                )

                        )

                    [1] => Array
                        (
                            [id] => 7
                            [user_id] => 52
                            [follower_id] => 26
                            [date_added] => 2017-09-22 16:35:23
                            [date_updated] => 2017-09-22 20:35:23


                        )

                        )
        )

I need to paginate (find) users and order by maximum number of followers.

//$this->User->virtualFields['total_flw'] = 'COUNT(`User.Followers`)';

$this->paginate = array(
             'conditions' => array( 'User.user_status'                  => 'user',
                'User.soft_delete NOT' => 'on',
                    'User.status NOT' => 'inactive',
                    'User.id NOT'                  => $this->Session->read('User.id'),
              $conditions,
            ),
            'recursive'  => 2,
            //'order'      => 'total_flw desc',
            'paramType'  => 'querystring',
            'limit'      => '24',
            'maxLimit'   => 100,
        );
        $listing_searched_users = $this->paginate('User');

Tried using virtual fields but not successful. Any help would be appreciated.

Answer 1

You need to join your hasMany model in your query and do the COUNT() in the ORDER BY part of the paginate find:-

$this->paginate = [
    'joins' => [
        [
            'table' => 'followers',
            'alias' => 'Follower',
            'type' => 'LEFT',
            'conditions' => 'Follower.user_id = User.id'
        ]
    ],
    'group' => ['User.id'],
    'order' => ['COUNT(User.id)' => 'DESC']
];

Containing the associated model won't work here as the relationship is a one-to-many association which means Cake would have to perform two separate queries which is why we use joins instead.