We had a performance issue wich had something to do Entities ( see link below) . Now, I am stumbling on a case where the query written in the ORM produce multiple copies of the same SQL query
$paies = $this->Paies->find()->contain(
[
'PaieHeures.Contrats.Localisations',
'PaieHeures' => function (Query $q) {
return $q->select(
[
'paie_id',
'date',
'h_payable' => $q->func()->sum('h_payable'),
'h_total' => $q->func()->sum('h_total'),
// and a few more fields...
'timesheet_id',
]
)->group(['date', 'taux_reg', 'taux_sup', 'rapport_journalier_id', 'timesheet_id'])->contain(
[
'Fonctions',
'Equipements',
'Tasks'
]
)->order('date');
},
'PaieMetrages' => function (Query $q) {
return $q->order(['date', 'debut']);
},
'PaieAutres',
'PaieAjustements',
'Employees',
'Entreprises',
]
)->order("Employees.last_name");
$paies->where(
function ($exp) use ($dateDebut, $checkedIds) {
return $exp
->eq('Paies.date', $dateDebut)
->in('Paies.id', $checkedIds);
}
);
To get one row, this produces 32 SQL queries.
SELECT
Paies.id AS `Paies__id`,
Paies.employee_id AS `Paies__employee_id`,
Paies.week AS `Paies__week`,
Paies.year AS `Paies__year`,
Paies.created AS `Paies__created`,
Paies.generated AS `Paies__generated`,
Paies.paie_ajustement_count AS `Paies__paie_ajustement_count`,
Paies.entreprise_id AS `Paies__entreprise_id`,
Paies.date AS `Paies__date`
FROM
paies Paies
WHERE
(
employee_id = 275
AND date = '2016-08-28'
AND Paies.entreprise_id IN (1)
)
LIMIT
1
…and some queries following it, is repeating itself 11 times. (why 11 ?) This time, I do not find a faulty code in an entity…
http://discourse.cakephp.org/t/function-in-entity-causes-too-many-sql-queries/1120