laravel - 如何使用 Laravel 使用查询生成器在左连接中添加附加条件

Question

我在 Laravel-5.8 中有这个 Eloquent Query Builder：

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

这用于获取员工休假余额。到目前为止，它工作正常。

但是有些休假类型只属于男性，只属于女性，有些属于两种性别。

如何将其添加到上面的查询中：

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

尤其是给：

hr_leave_type_details.leave_applicable_gender', [1, 3]

和

hr_leave_type_details.leave_applicable_gender', [1, 2]

谢谢

标签： laraveleloquentquery-builder

Answer 1

您可以使用when()查询构建器功能。

    /**
     * Apply the callback's query changes if the given "value" is true.
     *
     * @param  mixed  $value
     * @param  callable  $callback
     * @param  callable|null  $default
     * @return mixed|$this
     */
    public function when($value, $callback, $default = null)
    {
        if ($value) {
            return $callback($this, $value) ?: $this;
        } elseif ($default) {
            return $default($this, $value) ?: $this;
        }

        return $this;
    }

以下是文档中的几个示例：

// If $request->input('role') is truthy (in this context, it's not null),
// then filter users using $role variable.
$role = $request->input('role');

$users = DB::table('users')
                ->when($role, function ($query, $role) {
                    return $query->where('role_id', $role);
                })
                ->get();

// If $sortBy isn't null, order by $sortBy
// if $sortBy is null, order by 'name'
$sortBy = null;

$users = DB::table('users')
                ->when($sortBy, function ($query, $sortBy) {
                    return $query->orderBy($sortBy);
                }, function ($query) {
                    return $query->orderBy('name');
                })
                ->get();

将此应用于您的示例，我想您将拥有以下内容：

$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)
    ->when(
        $employeeegendercode == 0,
        function ($query) {
            return $query->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 3]);
        },
        function ($query) {
            return $query->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 2]);
        }
    )
    ->get();

为了清楚起见，我稍微夸大了缩进。对于像这样相对较小的更改，您可以使用 PHP 7.4 的速记闭包。

->when(
    $employeeegendercode == 0,
    fn ($query) => $query->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 3]),
    fn ($query) => $query->whereIn('hr_leave_type_details.leave_applicable_gender', [1, 2])
)

有关该主题的更多信息 https://laravel.com/docs/5.8/queries#conditional-clauses