Laravel ORM中whereHas与orWhere过滤$categoryNews失效求助

我正在开发一个新闻展示功能，对应的方法实现如下：

public function getAllNews($listBranches, $listGrade, $userId, $limit, $search, $language, $excludeId = null, $tagIds = null, $listBrandCode): LengthAwarePaginator
{
    $models = $this->model->with([
        'newsBrands', 'newsGrades', 'newsBranches',
        'userNewsLikeShare' => function ($query) use ($userId) {
            $query->where('user_id', $userId);
        },
    ])->whereIn('status', [NewsStatus::POSTED->value, NewsStatus::OUTSTANDING->value])
    ->where('date_post', '<=', now());

    $brandCode = $search['brand_code'] ?? BrandEnum::TDS->value;

    if (count($listBranches) > 0) {
        $models->whereHas('newsBrands', function ($query) use ($listBranches, $brandCode, $listBrandCode) {
            $query->where('abbreviation_name', $brandCode);
            if (!empty($listBranches) && in_array(strtolower($brandCode), $listBrandCode)) {
                $query->whereIn('branch_id', array_values($listBranches));
            }
        });
        if (!empty($listBranches)) {
            $models->whereHas('newsBranches', function ($query) use ($listBranches) {
                $query->whereIn('branch_id', $listBranches);
            })->orWhereDoesntHave('newsBranches');
        }
    }

    if (count($models->get()) == 0 || count($listBranches) == 0) {
        $models = $this->model->with([
            'newsBranches', 'newsGrades',
            'userNewsLikeShare' => function ($query) use ($userId) {
                $query->where('user_id', $userId);
            },
        ])->where('status', NewsStatus::POSTED->value);
        $models = $models->whereHas('newsBrands', function ($query) use ($brandCode) {
            $query->where('abbreviation_name', $brandCode);
        });
        $models = $models->whereHas('newsTypes', function ($q) {
            $q->where('news_types_id', NewTypeEvents::EVENTS->value);
        });
    }

    if (!empty($search['search'])) {
        $text_search = $search['search'];
        $models->where(function ($q) use ($text_search, $language) {
            $q->whereRaw("LOWER(json_extract(title, '$.$language')) COLLATE utf8mb4_0900_ai_ci like (?)", '%' . strtolower($text_search) . '%')
                ->orWhereRaw("LOWER(json_extract(description, '$.$language')) COLLATE utf8mb4_0900_ai_ci like (?)", '%' . strtolower($text_search) . '%');
        });
    }
    $models = $models->select([
        "id", "image", "title", "description", "likes", "views", "date_post", "is_pin",
        "link_share", "is_outstanding", "created_at", "updated_at", "status",
    ])->when($excludeId, function ($q) use ($excludeId) {
        $q->whereNot('id', $excludeId);
    })->when($tagIds, function ($q) use ($tagIds) {
        $q->whereHas('newsTags', function ($subQ) use ($tagIds) {
            $subQ->whereIn('tags_id', $tagIds);
        });
    })
    ->when(count($listGrade) > 0, function ($subQ) use ($listGrade) {
        $subQ->where(function ($subQ) use ($listGrade) {
            $subQ->whereDoesntHave('newsGrades')->orWhereHas('newsGrades', function ($subQ) use ($listGrade) {
                $subQ->whereIn('grade_id', $listGrade);
            });
        });
    })->whereHas('newsBrands', function ($query) use ($brandCode) {
        $query->where('abbreviation_name', $brandCode);
    });
    
    $orderBy = !empty($search['order_by']) ? $search['order_by'] : ['updated_at'];
    $sortedBy = !empty($search['sort_by']) ? $search['sort_by'] : 'desc';
    foreach ($orderBy as $value) {
        $models = $models->orderBy($value, $sortedBy);
    }
    $models->orderBy('status', 'desc');
    Log::channel('deferment_application')->info(
        $models->toSql().' - grade: '. implode(',', $listGrade)
        .' - branchID: '. implode(',', $listBranches)
    );

    $categoryNews = $search['category_news'] ?? [];
    if (count($categoryNews)) {
        $models->where(function ($query) use ($categoryNews) {
            foreach ($categoryNews as $category) {
                switch ($category) {
                    case NewsCategories::School->value:
                        $query->orWhere(function ($query) {
                            $query->whereHas('newsBranches')
                                ->whereDoesntHave('newsStudyPrograms')
                                ->whereDoesntHave('newsSchoolShips')
                                ->whereDoesntHave('newsGrades')
                                ->whereHas('newsTypes', function ($query) {
                                    $query->where('name->en', '!=', 'Events');
                                });
                        });

                        break;
                    case NewsCategories::GradeCategory->value:
                        $query->orWhere(function ($query) {
                            $query->whereHas('newsGrades')
                                ->whereHas('newsTypes', function ($query) {
                                    $query->where('name->en', '!=', 'Events');
                                });
                        });

                        break;
                    case NewsCategories::Event->value:
                        $query->orWhereHas('newsTypes', function ($query) {
                            $query->where('name->en', 'Events');
                        });

                        break;
                    case NewsCategories::SubjectCombinationCategory->value:
                        $query->orWhere(function ($query) {
                            $query->whereHas('newsSchoolShips')
                                ->whereDoesntHave('newsGrades')
                                ->whereHas('newsTypes', function ($query) {
                                    $query->where('name->en', '!=', 'Events');
                                });
                        });

                        break;
                    case NewsCategories::StudyProgram->value:
                        $query->orWhere(function ($query) {
                            $query->whereHas('newsStudyPrograms')
                                ->whereDoesntHave('newsSchoolShips')
                                ->whereDoesntHave('newsGrades')
                                ->whereHas('newsTypes', function ($query) {
                                    $query->where('name->en', '!=', 'Events');
                                });
                        });

                        break;
                }
            }
        });
    }

    return $models->paginate($limit);
}

模型关联关系如下：

public function newsStudyPrograms(): BelongsToMany
{
    return $this->belongsToMany(ProgramBase::class, 'news_config_study_program', 'news_id', 'study_program_id');
}

public function newsGrades(): BelongsToMany
{
    return $this->belongsToMany(GradeBase::class, 'news_config_grade', 'news_id', 'grade_id');
}

public function newsSchoolShips(): BelongsToMany
{
    return $this->belongsToMany(SchoolShipBase::class, 'news_config_school_ship', 'news_id', 'school_ship_id');
}

public function newsTypes(): BelongsToMany
{
    return $this->belongsToMany(NewsTypesBase::class, 'news_has_news_types', 'news_id', 'news_types_id');
}

问题 #

通过$categoryNews过滤时，原本用whereDoesntHave()排除的新闻仍会被返回。需求是：过滤新闻时仅显示选中类别的新闻，支持多选（例如同时选择NewsCategories::School和NewsCategories::GradeCategory，则只显示这两类新闻）。

解决方案 #

问题核心是分类过滤逻辑的拼接方式错误：当前用orWhere直接追加条件，导致分类过滤和之前的分支、年级等条件形成了OR关系，只要满足任一条件就会返回结果，而非严格匹配选中分类。

修改后的分类过滤代码如下：

$categoryNews = $search['category_news'] ?? [];
if (count($categoryNews)) {
    $models->where(function ($query) use ($categoryNews) {
        $isFirstCondition = true;
        foreach ($categoryNews as $category) {
            $categoryCondition = function ($query) use ($category) {
                switch ($category) {
                    case NewsCategories::School->value:
                        return $query->whereHas('newsBranches')
                            ->whereDoesntHave('newsStudyPrograms')
                            ->whereDoesntHave('newsSchoolShips')
                            ->whereDoesntHave('newsGrades')
                            ->whereHas('newsTypes', function ($q) {
                                $q->where('name->en', '!=', 'Events');
                            });
                    case NewsCategories::GradeCategory->value:
                        return $query->whereHas('newsGrades')
                            ->whereHas('newsTypes', function ($q) {
                                $q->where('name->en', '!=', 'Events');
                            });
                    case NewsCategories::Event->value:
                        return $query->whereHas('newsTypes', function ($q) {
                            $q->where('name->en', 'Events');
                        });
                    case NewsCategories::SubjectCombinationCategory->value:
                        return $query->whereHas('newsSchoolShips')
                            ->whereDoesntHave('newsGrades')
                            ->whereHas('newsTypes', function ($q) {
                                $q->where('name->en', '!=', 'Events');
                            });
                    case NewsCategories::StudyProgram->value:
                        return $query->whereHas('newsStudyPrograms')
                            ->whereDoesntHave('newsSchoolShips')
                            ->whereDoesntHave('newsGrades')
                            ->whereHas('newsTypes', function ($q) {
                                $q->where('name->en', '!=', 'Events');
                            });
                    default:
                        return $query;
                }
            };

            if ($isFirstCondition) {
                $query->where($categoryCondition);
                $isFirstCondition = false;
            } else {
                $query->orWhere($categoryCondition);
            }
        }
    });
}

关键修改说明 #

1. 独立逻辑块：所有分类条件包裹在一个where()闭包内，确保分类过滤是独立的判断逻辑，不会和之前的查询条件产生意外的OR关联。
2. 条件拼接优化：第一个分类条件用where，后续用orWhere，保证只有符合任一选中分类的新闻才会被返回。
3. 关联条件严谨性：每个分类的whereDoesntHave和whereHas组合更精准，确保完全匹配分类定义的新闻才会被选中。

另外，原代码中count($models->get()) == 0会触发全表查询，严重影响性能，建议改为$models->exists()判断：

if (!$models->exists() || count($listBranches) == 0) {
    // 重置查询逻辑
}

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