Laravel Count Different Value From a Table-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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