Level 63
Perhaps you could have a look at this when method for conditional clauses.
https://laravel.com/docs/8.x/queries#conditional-clauses
Tell me if it helps or not.
Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
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
Please or to participate in this conversation.