CakePHP3: クエリビルダーで SUM(CASE WHEN)

CakePHP PHP

こんな感じの SELECT 文を作りたい場合は、

SELECT
    user_id AS "user_id",
    (SUM(
            CASE
                WHEN hoge < 3 THEN count
            END
        )) AS "A",
    (SUM(
            CASE
                WHEN hoge >= 10 THEN count
            END
        )) AS "B"
FROM
    articles
GROUP BY
    user_id

こんな感じ

$query = $articles->find();

$caseA = $query->newExpr()
    ->addCase( //CASE
        $query->newExpr()->lt('hoge', 3), //WHEN
        $query->identifier('count'), //THEN
        'integer'
    );
    
$caseB = $query->newExpr()
    ->addCase(
        $query->newExpr()->gte('hoge', 10),
        $query->identifier('count'),
        'integer'
    );

$query->select([
    "user_id" => "user_id",
    "A" => $query->func()->sum($caseA),
    "B" => $query->func()->sum($caseA),
])
->group("user_id");