0

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.

Gaurav Neema
  • 146
  • 1
  • 1
  • 12

1 Answers1

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)))
ndm
  • 59,784
  • 9
  • 71
  • 110
  • This is generating the SQL you mentioned but the results contain "count_abc" = 1 or "count_abc" = 0 always. Is it because of extra brackets in query? – Gaurav Neema Feb 01 '19 at 13:56
  • 1
    @GauravNeema Well, that's a different problem... if the sample code is actually the code that you're using, then the problem is that you are counting a string, ie `app_num`. If that's supposed to be a column, then you need to pass it as an identifier expression instead, like `$query->identifier('app_num')`. Also there's a whitespace in the `app_num` string of the first case statement. – ndm Feb 01 '19 at 14:07
  • What will be the full statement with identifier? I have never worked with identifiers. – Gaurav Neema Feb 01 '19 at 14:18
  • 1
    @GauravNeema Just pass the shown snippet instead of the `app_num` string. You can also ditch the third argument then. – ndm Feb 01 '19 at 16:33