Join two tables, then GROUP BY and COUNT qualifying values and the grand total using Laravel Eloquent

Question

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?

Answer 1

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