Laravel Count Different Value From a Table

user_17275910 0
Viewed 49

I have tables name applications and status. The tables as follow:

Table: applications:

id status_id is_active
1 1 true
2 1 true
3 1 true
4 2 true
5 2 true

Table: status:

id desc
1 completed
2 pending
3 processed
4 failed

I'm trying to count the applications base on status. The expected output I want in blade:

Status Total
All 5
Completed 3
Pending 2
Processed 0
Failed 0

I am using eloquent in my controller to get the count, but i have to make different variable for each status.

$total = Application::select('*')
         ->where('is_active', 'true')
         ->count();

$complete = Application::select('*')
            ->where('application_sts', 1)
            ->where('is_active', 'true')
            ->count();

Is there any simple way to do this?

eloquent laravel
2 Answers

You can do it this way:

Status::select(DB::raw('status.id, status.desc, COUNT(*) AS count'))
        ->join('applications', 'status.id', '=', 'applications.status_id')
        ->groupBy('status.id')->get();

Or this way:

DB::table('applications')->select(DB::raw('status.id, status.desc, COUNT(*) AS count'))
           ->join('status', 'status.id', '=', 'applications.status_id')
           ->groupBy('status.id')->get();
user_77709190

You can have a base query and then apply only what's changing, this way you won't repeat the same code every time:

$query = Application::select('*')
         ->where('is_active', 'true');

$total = $query->clone()->count();

$complete = $query->clone()->where('application_sts', 1)->count();

Please note that you must clone the $query since this is not an immutable objet

user_37196890
Related