Laravel - How to combine multiple queries as one Eloquent Query

user_12510040 0
Viewed 645

In my Laravel-5.8, I have these four queries accessng the same model:

    $allLeaves               =       HrLeaveRequest::where('company_id', $userCompany)->whereYear('created_at', date('Y'))->count();
    $pendingLeaves           =       HrLeaveRequest::where('leave_status', 1)->where('company_id', $userCompany)->whereYear('created_at', date('Y'))->count();
    $rejectedLeaves          =       HrLeaveRequest::where('leave_status', 3)->where('company_id', $userCompany)->whereYear('created_at', date('Y'))->count();
    $approvedLeaves          =       HrLeaveRequest::where('leave_status', 4)->where('company_id', $userCompany)->whereYear('created_at', date('Y'))->count();

How do I combine the four queries as one

something similar to this

$gender_unpublished_record = HrEmployee::selectRaw('count(gender_code) as count,gender_code, if (gender_code = 1, "Male", "Female") as gender')->whereNotIn('employee_code', $publishedgoals)->where('company_id', $userCompany)->where('hr_status', 0)->groupBy('gender_code')->get();

The one above only have 0 or 1. But what I want to achive takes care of everything in the table, leave_status as 1, 3 and 4

Thank you

laravel
1 Answers

in your Company Model you should have the relation:

public function HrLeaveRequests()

{
return $this->hasMany(HrLeaveRequest::class,'company_id');
}

now you could use withCount:

 $value=Company::where('company_id',$userCompany)->withCount(['HrLeaveRequests as allLeaves'=>function($query){
            $query ->whereYear('created_at', date('Y'));
        },
            'HrLeaveRequests as pendingLeaves'=>function($query){
                $query->where('leave_status', 1)->whereYear('created_at', date('Y'));
            },
            'HrLeaveRequests as rejectedLeaves'=>function($query){
                $query->where('leave_status', 3)->whereYear('created_at', date('Y'));
            },
            'HrLeaveRequests as approvedLeaves'=>function($query){
                $query->where('leave_status', 4)->whereYear('created_at', date('Y'));
            },
        ])->get();
user_105735600
Related