Trying to use custom SQL function GREATEST

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
                        ]
                    ]
                ],
            )
            //
        ;

Just use raw query expressions:

https://book.cakephp.org/4/en/orm/query-builder.html#raw-expressions

Thank you for your answer,

Yes, I agree,

I’m going to give a try to your suggestion.

Anyway, I also plan to :

Blockquote[quote=“apricotpoodle, post:1, topic:12258”]
Anyway, I’m going to get around the obstacle by running raw SQL queries.
Database Basics - 4.x
[/quote]

I resolved this little pebble in my shoe by using good old stored procedures and functions, as well as a trigger directly in the database. The CakePHP framework will no longer need to execute unnecessary SelectQueries, and all is well that ends well.