CakePHP 3 putting un-necessary parentheses in SQL causing error

Question

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

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.

The code I have is as follows:

$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');

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

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)

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

Is this possible?

score 0 · Accepted Answer · answered Sep 05 '19 at 11:25

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.

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.

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'])
]);

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 putting un-necessary parentheses in SQL causing error

1 Answers1

Linked