[SOLVED] cakePHP 3 Query ifnull

cakePHP 3 Query ifnull

I wonder what would be the best way to prevent null results in a ResultSet. I'm on cake 3.5.13 and I'm using cases, like:

private function addCase($isforeign, $source)
{
    $query = $this->Sales->find();
    return $query->newExpr()
        ->addCase(
            $query->newExpr()->add([
                'Sales.isforeign' => $isforeign,
                'Sales.source' => $source
            ]),
            1,
            'integer');
}

I then put the return of my addCase function in

(..)
'sourcenationalcount' => $query->func()->sum($this->addCase(0, 1)),
(..)

Now it is possible that sourcenationalcount could become null. What would be the best way to return the value 0 instead. I wasn't able to find a func()->ifnull(). Should I use a formatResult() instead?

Solution

The functions builder can create any function you want, you just need to call it, and the magic method call handler of the builder will create a generic function call in case there is no concrete method implemented, ie func()->ifnull() will just work.

However, IFNULL is MySQL/SQLite specific, so in order to keep things as portable as possible, I'd suggest to simply use an ELSE case instead, one that selects 0 instead of NULL in case the conditions evaluate to FALSE.

$query
    ->newExpr()
    ->addCase(
        [
            $query->newExpr()->add([
                'Sales.isforeign' => $isforeign,
                'Sales.source' => $source
            ])
        ],
        [1, 0],
        ['integer', 'integer']
    );

That should generate SQL similar to:

CASE WHEN (Sales.isforeign = 0 AND Sales.source = 1) THEN 1 ELSE 0 END