Laravel Eloquent orWhere()与orWhereHas()查询无结果问题求助

问题背景 #

开发学生及接送家长的搜索功能，需求是学生姓名、接送家长手机号、接送家长身份证号任一匹配搜索关键词，就返回对应学生数据。当前问题：输入学生姓名能正常返回结果，但输入接送家长手机号（如62594283）或身份证号（如444343）时，返回空结果。

返回JSON示例 #

{"student_id": 1159,"full_name": "Student Test IT2","student_code": "HSTESTIT2","family_code": "No code","gender": 0,"student_pick_up": {"data": [{"id": 985,"first_name": "Father","last_name": "Test IT","full_name": "Father Test IT","parent_code": null,"phone_numbers": [{"id": 681,"country_code": "VN","phone_code": 84,"number": "62594283","pick_up_id": 985,"is_primary": 1,"created_at": "2023-07-16T03:35:51.000000Z","updated_at": null,"deleted_at": null}],"identity_card_number": "444343","is_long_term": 0,"start_date": null,"end_date": null,"gender": null,"is_representative": 0,"is_enable": 1},{"id": 991,"first_name": "Mother","last_name": "Test IT","full_name": "Mother Test IT","parent_code": null,"phone_numbers": [],"identity_card_number": null,"is_long_term": 0,"start_date": null,"end_date": null,"gender": null,"is_representative": 0,"is_enable": 1}]}}

现有查询代码 #

$filter = $request->get('input');

return $this->repository
            ->with(['classes' => $classes])
            ->whereHas('classes', $classes)
            ->with(['studentPickUp' => function ($query) use ($branchId, $currentDateTime) {
                $query->where('branch_id', $branchId);
                $query->where('is_effect', IsEffect::Effective->value);
                $query->where(function ($subQuery) use ($currentDateTime) {
                    $subQuery->where('is_long_term', IsLongIterm::Indefinite->value)
                    ->orWhere(function ($subQuery) use ($currentDateTime) {
                        $subQuery->where('start_date', '<=', $currentDateTime);
                        $subQuery->where('end_date', '>=', $currentDateTime);
                    });
                });
            },'studentPickUp.pickUp', 'studentPickUp.involvedType' => function ($query) {
                $query->select(['id', 'name', 'code']);
            },
            ])
            ->whereHas('studentPickUp', function ($query) use ($branchId) {
                $query->where('branch_id', $branchId);
            })
            ->when(is_string($filter) && $filter, function ($q) use ($lang, $filter, $studentRepresentativeIds) {
                $q->where(function ($query) use ($lang, $filter, $studentRepresentativeIds) {
                    $query->where(DB::raw("CAST(students.full_name->>'$.$lang' AS CHAR)"), 'like', '%' . $filter . '%')
                        ->orWhere(DB::raw('lower(students.student_code)'), 'like', '%' . strtolower($filter) . '%')
                        ->orWhereHas('studentPickUp', function ($query) use ($filter, $lang, $studentRepresentativeIds) {
                            $query->where('is_active', StudentPickUpStatus::ACTIVE->value)
                                ->where(function ($query) use ($filter, $lang, $studentRepresentativeIds) {
                                    $query->whereHas('studentPickUpPhoneNumbers', function ($builder) use ($filter) {
                                        $builder->where(DB::raw("LOCATE('$filter', student_pick_up_phone_numbers.number)"), '>', 0)
                                            ->where('student_pick_up_phone_numbers.is_primary', StudentPickUpPhoneNumber::IS_PRIMARY->value);
                                    })->orWhere(DB::raw("CAST(student_pick_up.full_name->>'$.$lang' AS CHAR)"), 'like', '%' . $filter . '%')
                                        ->orWhere(DB::raw("LOCATE('$filter', student_pick_up.identitycard_number)"), '>', 0)
                                        ->when(!empty($studentRepresentativeIds), function ($q) use ($studentRepresentativeIds) {
                                            $q->orWhereIn('student_representative_id', $studentRepresentativeIds);
                                        });
                                });
                        });
                });
            })
            ->orderBy($orderBy, $sortedBy)->paginate($request->get('limit') ?? 10);

模型关系代码 #

StudentBase 模型 #

class StudentBase extends ParentModel
{
    use HasTranslations;

    protected $table = 'students';

    public function studentPickUp(): HasMany
    {
        return $this->hasMany(StudentPickUp::class, 'student_id');
    }
}

StudentPickUpBase 模型 #

class StudentPickUpBase extends ParentModel
{
    use HasTranslations;

    public array $translatable = ['full_name', 'student_name', 'class_name'];

    public function studentPickUpPhoneNumbers(): HasMany
    {
        return $this->hasMany(StudentPickUpPhoneNumber::class, 'pick_up_id');
    }
}

问题排查点及解决方案 #

1. 身份证字段名不匹配 #

查询代码中使用了student_pick_up.identitycard_number，但从返回的JSON可以看到，实际字段名是identity_card_number（下划线分隔），字段名错误导致查询不到数据。

修复：将代码中LOCATE('$filter', student_pick_up.identitycard_number)改为LOCATE('$filter', student_pick_up.identity_card_number)

2. 接送人状态字段名错误 #

查询代码中用where('is_active', StudentPickUpStatus::ACTIVE->value)过滤接送人状态，但返回的JSON里接送人字段是is_enable，字段名不匹配导致符合条件的接送人被排除。

修复：将where('is_active', StudentPickUpStatus::ACTIVE->value)改为where('is_enable', StudentPickUpStatus::ACTIVE->value)，同时确认StudentPickUpStatus::ACTIVE的枚举值是否对应is_enable的有效状态（比如1代表启用）。

3. 手机号查询优化（可选） #

当前用LOCATE函数匹配手机号，虽然功能正常，但换成like查询更直观，也更容易维护：

替换：将

$builder->where(DB::raw("LOCATE('$filter', student_pick_up_phone_numbers.number)"), '>', 0)

改为

$builder->where('student_pick_up_phone_numbers.number', 'like', "%{$filter}%")

4. 关联查询条件一致性（可选） #

外层with(['studentPickUp' => ...])已经对接送人做了branch_id、is_effect、有效期的过滤，但内层whereHas('studentPickUp')只限制了branch_id和is_active（现在改为is_enable），可以将内层条件和外层保持一致，避免因条件不一致导致的匹配偏差：

优化：将内层whereHas('studentPickUp')的回调函数修改为：

function ($query) use ($filter, $lang, $studentRepresentativeIds, $branchId, $currentDateTime) {
    $query->where('branch_id', $branchId);
    $query->where('is_effect', IsEffect::Effective->value);
    $query->where(function ($subQuery) use ($currentDateTime) {
        $subQuery->where('is_long_term', IsLongIterm::Indefinite->value)
            ->orWhere(function ($subQuery) use ($currentDateTime) {
                $subQuery->where('start_date', '<=', $currentDateTime);
                $subQuery->where('end_date', '>=', $currentDateTime);
            });
    });
    $query->where('is_enable', StudentPickUpStatus::ACTIVE->value);
    // 后续的姓名/手机号/身份证查询逻辑...
}

验证修复 #

修改完成后，测试以下场景：

• 输入学生姓名，确认正常返回结果
• 输入接送家长手机号62594283，确认返回对应学生
• 输入接送家长身份证号444343，确认返回对应学生

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