SQL case query with DISTINCT in cakephp3 ORM

Question

I am trying to build a case query with distinct count in cakephp 3.

This is the query in SQL:

select COUNT(distinct CASE WHEN type = 'abc' THEN app_num END) as "count_abc",COUNT(distinct CASE WHEN type = 'xyz' THEN app_num END) as "count_xyz" from table;

Currently, I got this far:

$query = $this->find();

$abc_case = $query->newExpr()->addCase($query->newExpr()->add(['type' => 'abc']),' app_num','string');
$xyz_case = $query->newExpr()->addCase($query->newExpr()->add(['type' => 'xyz']),'app_num','string');


$query->select([
    "count_abc" => $query->func()->count($abc_case),
    "count_xyz" => $query->func()->count($xyz_case),
]);

But I can't apply distinct in this code.

Answer 1

Using keywords in functions has been a problem for quite some time, see for example this issue ticket: https://github.com/cakephp/cakephp/issues/10454.

This has been somewhat improved in https://github.com/cakephp/cakephp/pull/11410, so that it's now possible to (mis)use a function expression for DISTINCT as kind of a workaround, ie generate code like DISTINCT(expression), which works because the parentheses are being ignored, so to speak, as DISTINCT is not a function!

I'm not sure if this works because the SQL specifications explicitly allow parentheses to be used like that (also acting as a whitespace substitute), or because it's a side-effect, so maybe check that out before relying on it!

That being said, you can use the workaround from the linked PR until real aggregate function keyword support is being added, ie do something like this:

"count_abc" => $query->func()->count(
    $query->func()->DISTINCT([$abc_case])
)

This would generate SQL similar to:

(COUNT(DISTINCT(CASE WHEN ... END)))