Laravel Improve Sql Query WithCount using CASE

Hi i want to improve my Query for dashboard stats i use withCount but its slow so i try to to use CASE Sql My Code : 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);
}

the relation in my Model :

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()]);

}

Please need help

tisuchi

2 years ago

Level 70

@souheibmadi You can use eloquent here like this:

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

    $dataWilayas = Wilaya::select('wilaya.mat_wilaya', 'wilaya.nom_wilaya')
        ->withCount([
            'statsPerMonth as on_process' => function ($query) use ($ids) {
                $query->whereIn('id_stats', $ids);
            },
            'statsPerMonth as returned' => function ($query) {
                $query->where('id_stats', 3);
            },
            'statsPerMonth as delivered' => function ($query) {
                $query->where('id_stats', 4);
            },
        ])
        ->paginate(10);

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

And adjust your relationship:

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

1 like

NoLAstNamE

2 years ago

Level 8

@tisuchi Wow, didn't know this. Thanks!

souheibmadi

2 years ago

Level 1

@tisuchi thank you , but the query take 8 seconds i try tom improve more :/

tisuchi

2 years ago

Level 70

@souheibmadi How did you calculate the "8s"?

Have you installed the debugbar? If not, then install it. It will help you to identify the slow queries and N+1 issues.

souheibmadi

2 years ago

Level 1

@tisuchi yes i installed the debugar i have a large database

souheibmadi

2 years ago

Level 1

i improve it with : public function getWilayasApi() {

    $ids = Stats::where('order_stats', 5)->pluck('id_stats')->toArray(); 
    $now = Carbon::now();
    $startOfMonth = $now->copy()->startOfMonth();
    $endOfMonth = $now->copy()->endOfMonth();
    $dataWilayas = DB::table('wilaya')
    ->select('wilaya.mat_wilaya', 'wilaya.nom_wilaya')
    ->selectRaw('COUNT(CASE WHEN stats_colis.id_stats = 10 THEN stats_colis.id_colis END) as on_process')
    ->selectRaw('COUNT(CASE WHEN stats_colis.id_stats = 3 THEN stats_colis.id_colis END) as returned')
    ->selectRaw('COUNT(CASE WHEN stats_colis.id_stats = 4 THEN stats_colis.id_colis END) as delivered')
    ->leftJoin('colis', 'wilaya.mat_wilaya', '=', 'colis.wilaya_id')
    ->leftJoin('stats_colis', 'colis.id_colis', '=', 'stats_colis.id_colis')
    ->whereBetween('stats_colis.updated_at', [$startOfMonth, $endOfMonth])
    ->groupBy('wilaya.mat_wilaya', 'wilaya.nom_wilaya')
    ->paginate(10);

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

}

Please or to participate in this conversation.