Laravel 10中如何在Eloquent的with方法内添加when条件？

问题场景 #

使用Laravel 10框架，现有一个包含多层with关联的Eloquent查询可正常运行，需求是在关联查询的闭包内添加非必选条件（根据request的search参数过滤work的title），尝试用when方法实现但条件未生效，无报错但过滤逻辑不执行。

原始正常查询代码 #

return Result::where('school_id', $school->id)
        ->where('user_id', $user->id)
        ->with(['work' => function (Builder $query) use ($school, $request) {
            $query->where('school_id', $school->id)
                ->orderBy('expected_at')
                ->with(['subject' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with(['workType' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with(['classroom' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with('workStatus');
        }])
        ->paginate();

修改后when条件未生效的代码 #

return Result::where('school_id', $school->id)
        ->where('user_id', $user->id)
        ->with(['work' => function (Builder $query) use ($school, $request) {
            $query->where('school_id', $school->id)
                ->orderBy('expected_at')

                ->when(isset($request['search']), function ($query) use ($request) {
                    return $query->where('title', 'ilike', '%' . $request['search'] . '%');   
                })

                ->with(['subject' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with(['workType' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with(['classroom' => function (Builder $query) use ($school) {
                    $query->where('school_id', $school->id);
                }])
                ->with('workStatus');
        }])
        ->paginate();

解决方法 #

• 修正when方法的判断逻辑
  问题出在isset($request['search'])的判断上，Laravel的request对象中，即使参数存在但为空字符串，isset仍会返回true，但实际我们需要的是参数存在且不为空的场景。改用$request->filled('search')来判断，它会检查参数是否存在且非空：

  ->when($request->filled('search'), function ($query) use ($request) {
      return $query->where('title', 'ilike', '%' . $request->get('search') . '%');   
  })
  

• 规范闭包内的查询写法
  Laravel的when闭包中可以直接修改查询对象，不需要返回（返回也没问题），同时建议使用$request->search或$request->get('search')替代数组访问方式，更符合框架规范：

  ->when($request->filled('search'), function ($query) use ($request) {
      $query->where('title', 'ilike', '%' . $request->search . '%');   
  })
  

• 可选：简化多层with写法
  可以把多个关联查询合并到一个with数组中，让代码更简洁：

  ->with([
      'subject' => fn($q) => $q->where('school_id', $school->id),
      'workType' => fn($q) => $q->where('school_id', $school->id),
      'classroom' => fn($q) => $q->where('school_id', $school->id),
      'workStatus'
  ])
  

最终修正后的完整代码 #

return Result::where('school_id', $school->id)
        ->where('user_id', $user->id)
        ->with(['work' => function (Builder $query) use ($school, $request) {
            $query->where('school_id', $school->id)
                ->orderBy('expected_at')
                ->when($request->filled('search'), function ($query) use ($request) {
                    $query->where('title', 'ilike', '%' . $request->search . '%');   
                })
                ->with([
                    'subject' => fn($q) => $q->where('school_id', $school->id),
                    'workType' => fn($q) => $q->where('school_id', $school->id),
                    'classroom' => fn($q) => $q->where('school_id', $school->id),
                    'workStatus'
                ]);
        }])
        ->paginate();

不需要使用原生SQL，通过调整when的判断条件和规范查询写法即可解决问题。

内容的提问来源于stack exchange，提问作者Dom