Is orm/filtering conditions 2.X code is correct in 3.X ? Bug?


#1

Before filling a bug, i am wondering if 2.x filter in a find is correct in 3.x…

One teammate is using this code to filter a find :

$creditTable = TableRegistry::get(‘Credits’);
$option_barrage[‘contain’] = [‘Emplois’]; // Credits belongs to Emplois
$option_barrage[‘conditions’][‘date_barrage >’] = $date_barrage; // Time object !
$option_barrage[‘conditions’][‘Emplois.date_debut >=’] = $dd; // Time object !
$option_barrage[‘conditions’][‘Emplois.date_fin <=’] = $df; // Time object !
$info_barrage = $creditTable->find(‘all’,$option_barrage);

This is 2.X code style and 99% working on cake 3.4.5. So it seems correct, is it ?

Our problem is that dd and df are incorrectly formatted (using locale ?) in generated SQL but date_barrage is not !

SQL :

select …
Agents.modified AS Agents__modified
FROM
credits Credits
LEFT JOIN emplois Emplois ON Emplois.id = (Credits.emploi_id)
WHERE
(
date_barrage > '2016-11-02’
AND Emplois.date_debut >= '01/09/2015 00:00’
AND Emplois.date_fin <= ‘31/08/2016 00:00’
)

Using a matching syntax, dates formatting is ok :

$query = $creditTable->find(‘all’);
$query->matching(‘Emplois’, function ($q) use ($dd, $df) {
return $q->where([
‘Emplois.date_debut >=’ => $dd,
‘Emplois.date_fin >=’ => $df,
]);
});

Produce :

SELECT …
FROM
credits Credits
INNER JOIN emplois Emplois ON (
Emplois.date_debut >= '2015-09-01’
AND Emplois.date_fin >= '2016-08-31’
AND Emplois.id = (Credits.emploi_id)
)


#2

mysql> desc emplois;
±-----------------±----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±-----------------±----------------±-----±----±--------±---------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| agent_id | varchar(64) | YES | MUL | NULL | |
| date_debut | date | YES | | NULL | |
| date_fin | date | YES | | NULL | |
| scenario_id | int(6) unsigned | YES | MUL | NULL | |
| quotite_id | int(6) unsigned | YES | MUL | NULL | |
| contractuel | tinyint(4) | YES | | 0 | |
| modification_par | varchar(64) | YES | | NULL | |
| created | datetime | YES | | NULL | |
| modified | datetime | YES | | NULL | |
±-----------------±----------------±-----±----±--------±---------------+
10 rows in set (0.00 sec)

mysql> desc credits;
±----------------±----------------±-----±----±--------±---------------+
| Field | Type | Null | Key | Default | Extra |
±----------------±----------------±-----±----±--------±---------------+
| id | int(6) unsigned | NO | PRI | NULL | auto_increment |
| date_debut | datetime | YES | | NULL | |
| date_fin | datetime | YES | | NULL | |
| date_barrage | date | YES | | NULL | |
| agent_id | varchar(64) | YES | MUL | NULL | |
| nb_heure | int(6) unsigned | YES | | 0 | |
| solde_nb_heure | int(6) unsigned | YES | | 0 | |
| credittype_id | int(6) unsigned | YES | MUL | NULL | |
| emploi_id | int(6) unsigned | YES | MUL | NULL | |
| observation | text | YES | | NULL | |
| bonification | float | NO | | 0 | |
| date_validation | date | YES | | NULL | |
| validation_par | varchar(64) | YES | MUL | NULL | |
| created | datetime | YES | | NULL | |
| modified | datetime | YES | | NULL | |
±----------------±----------------±-----±----±--------±---------------+
15 rows in set (0.00 sec)

mysql>