# Trying to use custom SQL function GREATEST

**URL:** <https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258>\
**Category:** Need Help\
**Tags:** behavior\
**Created:** [September 19, 2024, 1:24pm UTC](https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258 "2024-09-19T13:24:19Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![apricotpoodle](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@apricotpoodle](https://discourse.cakephp.org/u/apricotpoodle)\
**Post date:** [September 19, 2024, 1:24pm UTC](https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258/1 "2024-09-19T13:24:19Z")

</div>

# 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](https://book.cakephp.org/4/en/orm/query-builder.html#custom-functions)

## 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

```SQL
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

```SQL
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

```SQL
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.](https://book.cakephp.org/4/en/orm/database-basics.html#running-select-statements)

## here is, how I try to build the GREATEST custom function :

```php
        /**
         * @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
                        ]
                    ]
                ],
            )
            //
        ;

```

---

<div class="post-metadata">

**Author:** ![KevinPfeifer](https://yyz1.discourse-cdn.com/flex029/user_avatar/discourse.cakephp.org/kevinpfeifer/32/3365_2.png) [@KevinPfeifer](https://discourse.cakephp.org/u/KevinPfeifer)\
**Post date:** [September 19, 2024, 1:42pm UTC](https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258/2 "2024-09-19T13:42:53Z")

</div>

Just use raw query expressions:

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

---

<div class="post-metadata">

**Author:** ![apricotpoodle](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@apricotpoodle](https://discourse.cakephp.org/u/apricotpoodle)\
**Post date:** [September 19, 2024, 3:20pm UTC](https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258/3 "2024-09-19T15:20:07Z")

</div>

### 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](https://book.cakephp.org/4/en/orm/database-basics.html#running-select-statements)  
> [/quote]

---

<div class="post-metadata">

**Author:** ![apricotpoodle](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@apricotpoodle](https://discourse.cakephp.org/u/apricotpoodle)\
**Post date:** [September 20, 2024, 1:28pm UTC](https://discourse.cakephp.org/t/trying-to-use-custom-sql-function-greatest/12258/4 "2024-09-20T13:28:17Z")

</div>

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.