Cakephp 3 order on request generates 14 queries with associated tables instead of 1

My cakephp3 app is working very well and now I want to order some queries on related table fields.

The query works fine when ordering a field in the current model. But if I choose to order by field Models.field

in the related table, I now get 14 sql queries instead of one. But the real problem is what is ORDER BY

not included in the request.

I am entering a CoursesEmployees model which has employee_id and course_id fields.

CoursesEmployeesController.php

public function certificate() {
    $this->paginate = [
        'sortWhitelist' => [
            'Employees.surname', 'Courses.name', 'date_completed'
        ],
        'conditions' => ['CoursesEmployees.completed' => true, 'Employees.user_id' => $this->Auth->user('id')],
        'contain' => ['Employees', 'Courses'],
        'order' => ['Employees.name'=>'asc']
    ];
    $this->set('coursesEmployees', $this->paginate($this->CoursesEmployees));
}

      

        
        
        
      

    

If I order 'order' => ['date_created'=>'asc']

, I get one request and everything works fine.

SELECT
    CoursesEmployees.id AS `CoursesEmployees__id`,
    CoursesEmployees.employee_id AS `CoursesEmployees__employee_id`,
    CoursesEmployees.course_id AS `CoursesEmployees__course_id`,
    CoursesEmployees.course_module_id AS `CoursesEmployees__course_module_id`,
    CoursesEmployees.cid AS `CoursesEmployees__cid`,
    CoursesEmployees.progress AS `CoursesEmployees__progress`,
    CoursesEmployees.modified AS `CoursesEmployees__modified`,
    CoursesEmployees.created AS `CoursesEmployees__created`,
    CoursesEmployees.completed AS `CoursesEmployees__completed`,
    CoursesEmployees.date_completed AS `CoursesEmployees__date_completed`,
    Employees.id AS `Employees__id`,
    Employees.user_id AS `Employees__user_id`,
    Employees.hotel_id AS `Employees__hotel_id`,
    Employees.name AS `Employees__name`,
    Employees.email AS `Employees__email`,
    Employees.surname AS `Employees__surname`,
    Employees.employee_num AS `Employees__employee_num`,
    Employees.modified AS `Employees__modified`,
    Employees.created AS `Employees__created`,
    Courses.id AS `Courses__id`,
    Courses.name AS `Courses__name`,
    Courses.course_lenght AS `Courses__course_lenght` 
FROM
    courses_employees CoursesEmployees 
INNER JOIN
    employees Employees 
        ON Employees.id = (
            CoursesEmployees.employee_id
        ) 
INNER JOIN
    courses Courses 
        ON Courses.id = (
            CoursesEmployees.course_id
        ) 
WHERE
    (
        CoursesEmployees.completed = 1 
        AND Employees.user_id = '1'
    ) 
ORDER BY
    CoursesEmployees.date_completed asc LIMIT 20 OFFSET 0

      

        
        
        
      

    

If I am 'order' => ['Employees.name'=>'asc']

(or any random text for the field name), I get 14 sql queries and the main query is out of order at the end as it should.

SHOW FULL COLUMNS FROM `courses_employees`  10  1

SHOW INDEXES FROM `courses_employees`   1   0

SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'deep_mind' AND kcu.TABLE_NAME = 'courses_employees' and rc.TABLE_NAME = 'courses_employees'    0   0

SHOW TABLE STATUS WHERE Name = 'courses_employees'  1   0

SHOW FULL COLUMNS FROM `employees`  9   1

SHOW INDEXES FROM `employees`   1   0

SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'deep_mind' AND kcu.TABLE_NAME = 'employees' and rc.TABLE_NAME = 'employees'    0   0

SHOW TABLE STATUS WHERE Name = 'employees'  1   0

SHOW FULL COLUMNS FROM `courses`    3   1

SHOW INDEXES FROM `courses` 1   0

SELECT * FROM information_schema.key_column_usage AS kcu INNER JOIN information_schema.referential_constraints AS rc ON (kcu.CONSTRAINT_NAME = rc.CONSTRAINT_NAME) WHERE kcu.TABLE_SCHEMA = 'deep_mind' AND kcu.TABLE_NAME = 'courses' and rc.TABLE_NAME = 'courses'    0   0

SHOW TABLE STATUS WHERE Name = 'courses'    1   0

SELECT
    CoursesEmployees.id AS `CoursesEmployees__id`,
    CoursesEmployees.employee_id AS `CoursesEmployees__employee_id`,
    CoursesEmployees.course_id AS `CoursesEmployees__course_id`,
    CoursesEmployees.course_module_id AS `CoursesEmployees__course_module_id`,
    CoursesEmployees.cid AS `CoursesEmployees__cid`,
    CoursesEmployees.progress AS `CoursesEmployees__progress`,
    CoursesEmployees.modified AS `CoursesEmployees__modified`,
    CoursesEmployees.created AS `CoursesEmployees__created`,
    CoursesEmployees.completed AS `CoursesEmployees__completed`,
    CoursesEmployees.date_completed AS `CoursesEmployees__date_completed`,
    Employees.id AS `Employees__id`,
    Employees.user_id AS `Employees__user_id`,
    Employees.hotel_id AS `Employees__hotel_id`,
    Employees.name AS `Employees__name`,
    Employees.email AS `Employees__email`,
    Employees.surname AS `Employees__surname`,
    Employees.employee_num AS `Employees__employee_num`,
    Employees.modified AS `Employees__modified`,
    Employees.created AS `Employees__created`,
    Courses.id AS `Courses__id`,
    Courses.name AS `Courses__name`,
    Courses.course_lenght AS `Courses__course_lenght` 
FROM
    courses_employees CoursesEmployees 
INNER JOIN
    employees Employees 
        ON Employees.id = (
            CoursesEmployees.employee_id
        ) 
INNER JOIN
    courses Courses 
        ON Courses.id = (
            CoursesEmployees.course_id
        ) 
WHERE
    (
        CoursesEmployees.completed = 1 
        AND Employees.user_id = '1'
    ) LIMIT 20 OFFSET 0