如何在Cake PHP 3分页中添加子查询以获得平均值

Question

I'm doing Cake PHP 3x pagination and have to do mysql avg function and get the result.

SQL query is:

$conn->prepare('SELECT u.id, c.user_id, u.name, u.email, c.phone_number, c.candidate_rating, c.modified, c.city, c.created, 
(SELECT AVG(internal_rating) FROM schedule WHERE gigstr = c.user_id AND internal_rating != 0) AS rating_avg
                        FROM user u
                        INNER JOIN candidate c ON u.id = c.user_id
                        ORDER BY u.id DESC
                       ');

Pagination code is:

$this->paginate = [
            'fields' => ['user_id', 'phone_number', 'country', 'city', 'created', 'User.email', 'User.name'],
            'order' => [
                'User.id' => 'desc'
            ],
            'contain' => ['User'],
            'limit' => 30
        ];
$candidates = $this->paginate($this->Candidate);

My code is in the Candidate Controller and I need to add that sub query get the average into pagination. sub query :

(SELECT AVG(internal_rating) FROM schedule WHERE gigstr = c.user_id AND internal_rating != 0) AS rating_avg

schedule is also a separate model (table)