Laravel中使用CASE优化withCount仪表盘统计SQL查询求助

原使用withCount方式做仪表盘统计时性能较差，尝试用CASE语句改写查询，但遇到问题需要帮助。相关代码如下：

控制器方法 #

public function getWilayasApi()
{
    $ids = Stats::where('order_stats',5)->pluck('id_stats')->toArray();

    $dataWilayas  = Wilaya::select('wilaya.mat_wilaya', 'wilaya.nom_wilaya')
    ->with([ 'statsPerMonth' => function ($query)  { $query->select(DB::raw('
        SUM(CASE
            WHEN stats_colis.id_stats = 10 THEN 1
            ELSE 0
        END) AS on_process
    '),
    DB::raw('
        SUM(CASE
            WHEN stats_colis.id_stats = 3 THEN 1
            ELSE 0
        END) AS returned
    '),
    DB::raw('
        SUM(CASE
            WHEN stats_colis.id_stats = 4 THEN 1
            ELSE 0
        END) AS delivered
    ')); } ])
    ->paginate(10);

    return response()->json($dataWilayas);
}

模型关联 #

public function statsPerMonth()
{
    return $this->hasManyThrough(StatsColis::class, Colis::class, 'wilaya_id', 'id_colis', 'mat_wilaya', 'id_colis')
    ->whereBetween(
        'stats_colis.updated_at',
        [Carbon::now()->startOfMonth(), Carbon::now()->endOfMonth()]);
}

内容的提问来源于stack exchange，提问作者MADI