Cakephp query with WHEN CASE

Question

I have below query.

$parents = $this->SurveyQuestion->find('all',array('fields' => array( 
                       'SurveyQuestion.id', 
                       '((CASE WHEN SurveyQuestion.tree_label%2="" THEN \'tree_label\' ELSE \'label\' END)) AS plabel' 
                   ),'conditions' => 
                array('`SurveyQuestion`.`id` <>' => $id), 'recursive' => -1));

I want result like below. I want result from tree_label column if it is not NULL else I want result from label column. Above query returns wrong value.. Can anyone please help ?

Answer 1

Giving you an similar example, please try like this :

SELECT
COUNT(CASE WHEN published = 'Y' THEN 1 END) AS number_published,
COUNT(CASE WHEN published = 'N' THEN 1 END) AS number_unpublished
FROM articles

$query = $articles->find();
$publishedCase = $query->newExpr()
->addCase(
    $query->newExpr()->add(['published' => 'Y']),
    1,
    'integer'
);

$unpublishedCase = $query->newExpr()
->addCase(
    $query->newExpr()->add(['published' => 'N']),
    1,
    'integer'
);

 $query->select([
'number_published' => $query->func()->count($publishedCase),
'number_unpublished' => $query->func()->count($unpublishedCase)
]);

hope it helps :)