Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

noblemfd's avatar

How to add a additional condition in left join using query builder

I have this Eloquent Query Builder in Laravel:

    $userCompany                    = Auth::user()->company_id;    
    $userEmployee                   = Auth::user()->employee_id;
    $employeeCode                   = Auth::user()->employee_code;
$employeeemptypeid              = HrEmployee::where('employee_code', $employeeCode)->where('company_id', $userCompany)->pluck('employee_type_id')->first();
    $employeeegendercode            = HrEmployee::where('employee_code', $employeeCode)->where('company_id', $userCompany)->pluck('gender_code')->first(); 


    $leaveBalance = DB::table('hr_leave_types AS lt')
                ->leftJoin('hr_leave_type_details AS ltd', function($join) use ($userCompany)
                    {
                        $join->on('ltd.leave_type_id', '=', 'lt.id')
                        ->where('ltd.company_id', '=', $userCompany)
                        ->where('ltd.employee_type_id', '=', $employeeemptypeid);
                    })
                ->leftJoin('hr_leave_requests AS lr', function($join) use ($userCompany, $userEmployee)
                    {
                        $join->on('lr.leave_type_id', '=', 'lt.id')
                        ->where('lr.company_id', '=', $userCompany)
                        ->where('lr.employee_id', '=', $userEmployee)
                        ->whereYear('lr.commencement_date', '=', date('Y'))
                        ->where('lr.leave_status', 4);
                    })                        
                ->leftJoin('hr_employees AS e', function($join) use ($userCompany, $userEmployee)
                    {
                        $join->on('e.id', '=', 'lr.employee_id')
                        ->where('e.company_id', '=', $userCompany)
                        ->where('e.id', '=', $userEmployee)
                        ->where('e.employee_type_id', '=', 'ltd.employee_type_id');
                    })  
                ->where('lt.company_id', '=', $userCompany)

                ->select(
                        'lt.leave_type_name as leaveCategory',
                        'ltd.no_of_days as applicableLeave',
                        DB::raw("IFNULL(SUM(lr.no_of_days),0) as approvedLeave")

                       )
                  ->groupBy('lt.leave_type_name', 'e.id')
                ->get();  

This is used to get the Employee Leave Balance. It was working fine till this point.

But there are some leave types that belong to only male, only female and some to both genders.

How do I add this to the query above:

    if($employeeegendercode == 0) 
    {
        $leavetypes                     = HrLeaveType::join('hr_leave_type_details', 'hr_leave_type_details.leave_type_id', '=', 'hr_leave_types.id')->select('hr_leave_types.id as id', 'hr_leave_types.leave_type_name')->where('hr_leave_types.company_id', $userCompany)->where('hr_leave_type_details.employee_type_id', $employeeemptypeid)->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 3])->get();
    }else{
        $leavetypes                     = HrLeaveType::join('hr_leave_type_details', 'hr_leave_type_details.leave_type_id', '=', 'hr_leave_types.id')->select('hr_leave_types.id as id', 'hr_leave_types.leave_type_name')->where('hr_leave_types.company_id', $userCompany)->where('hr_leave_type_details.employee_type_id', $employeeemptypeid)->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 2])->get();
    }

especially for:

hr_leave_type_details.leave_applicable_gender', [1, 3]

and

hr_leave_type_details.leave_applicable_gender', [1, 2]

Thanks

0 likes
1 reply

Please or to participate in this conversation.