Trying to Use a custom SQL function GREATEST
Note : the project is in french environment, so some comment and variable name are in french.
They use Cake PHP 4.x
I’m trying to use the query builder, to build a vanilla SQL request with some time diff calculation.
Please read all as it’s quite long.
Documentation CakePhp I based my attempt on
What I expect
That’s the SQL request I made by hand, and try to replicate by using CakePhp QueryBuilder.
Stay focused on GREATEST function
SELECT
GREATEST(TIMESTAMPDIFF(MINUTE,TIME(ht.fin_time),TIME(d.derniere_voiture_at)),0) AS feu
FROM distributions d
JOIN parutions p ON d.parution_id = p.id
JOIN horaire_theoriques ht ON p.production_jour_id = ht.production_jour_id
JOIN n1_sections ns ON d.n1_section_id = ns.id
;
What I get
First Attempt
I get only one parameter, 0
SELECT (GREATEST(0)) AS feu
FROM distributions Distributions
INNER JOIN parutions Parutions ON Distributions.parution_id = :c0
INNER JOIN horaire_theoriques HoraireTheoriques ON Parutions.production_jour_id = :c1
INNER JOIN n1_sections N1Sections ON Distributions.n1_section_id = :c2
Second Attempt
Still only one part of the parameters
SELECT (GREATEST(TIMESTAMPDIFF(MINUTE, TIME(HoraireTheoriques.fin_time), TIME(Distributions.derniere_voiture_at)))) AS feu
FROM distributions Distributions
INNER JOIN parutions Parutions ON Distributions.parution_id = :c0
INNER JOIN horaire_theoriques HoraireTheoriques ON Parutions.production_jour_id = :c1
INNER JOIN n1_sections N1Sections ON Distributions.n1_section_id = :c2
CONCLUSION
- GREATEST should be able to manage several parameters, but seems only able to handle one, the last one ?
QUESTIONS :
Is that a bug in Cake parsing GREATEST command ?
Did I build my code in the wrong way ?
Anyway, I’m going to get around the obstacle by running raw SQL queries.
here is, how I try to build the GREATEST custom function :
/**
* @var \Cake\ORM\Query\SelectQuery $q
*/
$q = $tbl->find();
$finTime = $q->func()->TIME(
[
$tblNameTheo . '.' . HoraireTheorique::FIELD_FIN_TIME => 'identifier',
]
);
$lastCar = $q->func()->TIME(
[
$ntbl . '.' . Distribution::FIELD_DERNIERE_VOITURE_AT => 'identifier',
]
);
$diff = $q->func()->TIMESTAMPDIFF(
[
"MINUTE" => 'literal',
$finTime,
$lastCar,
]
);
$greatest = $q->func()->GREATEST(
[
$q->func()->TIMESTAMPDIFF(
[
"MINUTE" => 'literal',
$finTime,
$dernVoiture,
]
)
,"0" => 'literal' // If I comment this line the previous custom parameter is handled
]
);
$q->select(
[
'feu' => $greatest,
]
)
->join(
[
$ntblParutions =>
[
'table' => Inflector::tableize($ntblParutions),
'type' => 'INNER',
'conditions' => [
$ntbl . '.' . Distribution::FIELD_PARUTION_ID
=> $ntblParutions . '.' . Parution::FIELD_ID
],
],
$ntblHorTheo =>
[
'table' => Inflector::tableize($ntblHorTheo),
'type' => 'INNER',
'conditions' => [
$ntblParutions . '.' . Parution::FIELD_PRODUCTION_JOUR_ID
=> $ntblHorTheo . '.' . HoraireTheorique::FIELD_PRODUCTION_JOUR_ID
]
],
$ntblN1Section =>
[
'table' => Inflector::tableize($ntblN1Section),
'type' => 'INNER',
'conditions' =>
[
$ntbl . '.' . Distribution::FIELD_N1_SECTION_ID
=> $ntblN1Section . '.' . N1Section::FIELD_ID
]
]
],
)
//
;