cakePHP 3查询ifnull [英] cakePHP 3 Query ifnull
问题描述
我想知道什么是防止ResultSet中出现空结果的最佳方法。我在蛋糕3.5.13上,并且正在使用案例,例如:
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');
}
然后我将addCase函数的返回值放在
I then put the return of my addCase function in
(..)
'sourcenationalcount' => $query->func()->sum($this->addCase(0, 1)),
(..)
现在,sourcenationalcount可能会变为空。最好是返回值0的最佳方法。我找不到 func()-> ifnull()
。我应该使用 formatResult()
吗?
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?
推荐答案
函数生成器可以创建所需的任何函数,只需调用它,如果没有实现具体方法,则生成器的magic方法调用处理程序将创建泛型函数调用,即 func()-> ; ifnull()
可以正常工作。
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.
但是, IFNULL
是MySQL / SQLite具体来说,因此为了使内容尽可能便于携带,我建议您仅使用 ELSE
案例,选择 0 如果条件评估为
FALSE
,则使用code>而不是 NULL
。
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']
);
应生成类似于以下内容的SQL:
That should generate SQL similar to:
CASE WHEN (Sales.isforeign = 0 AND Sales.source = 1) THEN 1 ELSE 0 END
另请参见
- Cookbook > Database Access & ORM > Query Builder > Using SQL Functions
- Cookbook > Database Access & ORM > Query Builder > Using SQL Functions > Case Statements
这篇关于cakePHP 3查询ifnull的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!