CakePHP 3在SQL中放入不必要的括号导致错误 [英] CakePHP 3 putting un-necessary parentheses in SQL causing error

问题描述

CakePHP 3.7。尝试使用ORM编写包含MySQL COALESCE 条件的查询。

CakePHP 3.7. Trying to use the ORM to write a query which contains a MySQL COALESCE condition.

关于 CakePHP 3-如何在查询生成器中编写COALESCE（...）？，最终不得不使用 newExpr（）手动编写，因为这是给定的解决方案。

Followed advice on CakePHP 3 - How to write COALESCE(...) in query builder? and ended up having to write it manually using newExpr() as this was the given solution.

我的代码如下：

$TblRegulatoryAlerts = TableRegistry::getTableLocator()->get('TblRegulatoryAlerts');
$subscribed_to = $TblRegulatoryAlerts->getUserRegulations($u_id, $o_id, false); 

$query = $this->find()
        ->contain('Filters.Groups.Regulations')
        ->select(['id', 'date', 'comment', 'Filters.label', 'Filters.anchor', 'Groups.label']);

$query->select($query->newExpr('COALESCE((SELECT COUNT(*) 
        FROM revision_filters_substances 
        WHERE revision_filter_id = RevisionFilters.id), 0) AS count_substances'));

$query->where(['date >=' => $date_start, 'date <=' => $date_end, 'Regulations.id' => $regulation_id, 'Filters.id IN' => $subscribed_to]);

$query->enableHydration(false)->orderDesc('date');

这将产生以下SQL（ debug（$ query-> sql（））：

This produces the following SQL (output of debug($query->sql()):

SELECT RevisionFilters.id AS `RevisionFilters__id`, RevisionFilters.date AS `RevisionFilters__date`, RevisionFilters.comment AS `RevisionFilters__comment`, Filters.label AS `Filters__label`, Filters.anchor AS `Filters__anchor`, Groups.label AS `Groups__label`, (COALESCE((SELECT COUNT(*) FROM revision_filters_substances WHERE revision_filter_id = RevisionFilters.id), 0) AS count_substances) FROM revision_filters RevisionFilters INNER JOIN dev_hub_subdb.filters Filters ON Filters.id = (RevisionFilters.filter_id) INNER JOIN dev_hub_subdb.groups Groups ON Groups.id = (Filters.group_id) INNER JOIN dev_hub_subdb.regulations Regulations ON Regulations.id = (Groups.regulation_id) WHERE ...

不幸的是，这没有执行，因为Cake在 COALESCE 政治家周围加上了不必要的括号

Unfortunately this doesn't execute because Cake is putting in un-necessary parentheses surrounding the COALESCE statement, which changes the SQL.

在上面的代码中，它生成：

In the above code it generates:

(COALESCE((SELECT COUNT(*) FROM revision_filters_substances WHERE revision_filter_id = RevisionFilters.id), 0) AS count_substances)

它需要省略 COALESCE 的括号，所以只是：

Whereas it needs to omit the parentheses surrounding COALESCE so it's just:

COALESCE((SELECT COUNT(*) FROM revision_filters_substances WHERE revision_filter_id = RevisionFilters.id), 0) AS count_substances

有可能吗？

推荐答案

不要在表达式中指定别名，而是使用 key => Query :: select（）方法的值语法，例如：

Don't specify the alias in the expression, instead specify it using the key => value syntax of the Query::select() method, like this:

$query->select([
    'count_substances' => $query->newExpr('...')
]);

它仍将表达式括在括号中，但由于它不包括在内，因此将是有效的

It would still wrap the expression in parentheses, but that would then be valid as it doesn't include the alias.

也就是说，使用函数生成器 coalesque（）方法应该可以正常工作，问题描述如下可以使用 key =>值语法，其中值可以指定参数的类型，例如 [’Nodes.value’=> 'identifier'] ，否则它将值绑定为字符串。

That being said, using the function builders coalesque() method should work fine, the problem described in the linked question can be fixed by using the key => value syntax too, where the value can specify the kind of the argument, like ['Nodes.value' => 'identifier'], without that it would bind the value as a string.

但是您的示例应该不会出现任何此类问题，使用函数构建器 coalesce（）方法应该可以正常工作。

However there shouldn't be any such problem with your example, using the function builders coalesce() method should work fine.

$query->select([
    'count_substances' => $query->func()->coalesce($countSubquery, 1, ['integer'])
]);

type参数是可选的，没有它，它将与大多数DBMS一起使用，但是为了最大程度地兼容它应该被指定为正确地绑定整数值，它也会自动将函数的返回类型（强制类型）设置为 integer 。

The type argument is kinda optional, it would work with most DBMS without it, but for maximum compatibility it should be specified so that the integer value is being bound properly, also it will automatically set the return type of the function (the casting type) to integer too.

这篇关于CakePHP 3在SQL中放入不必要的括号导致错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！