My cakephp3 app works very well and now I wish to order some of the queries by the associated table fields.
The query works fine when ordered by a field in the current model. But if I choose to order by a field Models.field in an associated table I now get 14 sql queries rather than the one. But the real problem is that the ORDER BY is not included in the query.
I am in the 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 by 'order' => ['date_created'=>'asc'] I get one query and all 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 'order' => ['Employees.name'=>'asc'](or any random text for the field name) I get 14 sql queries and the main query does not have the 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
If you re-run the script immediately, you should see those queries disappearing.
These queries are being used to grab data for generating schemas and other metadata, and by default this should happen rather rarely as the data is being cached, given that
cacheMetadatais enabled in your apps datasource configuration.By default the data is being cached for 2 minutes, check your apps cache config for
_cake_model_. This is the value that is going to be used when the app is running in debug mode. When not in debug mode, the duration is by default being set to 1 year, check your appsboostrap.php.See also
As for your missing order field problem, you are missing the
Employees.namefield in your whitelisting, however even when whitelisting fields of other models it doesn't seem to work.That seems to be a bug in the paginator, namely in the
PaginatorComponent::_prefix()method, where the field is being swallowed, finally resulting in an emptyorderoption being passed to the query.Looks like this has already been reported and fixed for the upcoming 3.0.7 release. If you can't wait, grab the master branch or apply the patch.