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?
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
See also
- Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
- Cookbook > Database Access & ORM > Query Builder > Using SQL Functions > Case Statements