Laravel结合whereHas与Collections实现关联表相关性排序方案咨询

现有逻辑说明 #

现有可运行代码逻辑如下：首先检索users表匹配关键词的记录，再通过whereHas()方法查询中间表category_user，筛选出关联了指定搜索分类的用户，当前逻辑可正常获取全部符合条件的记录。
本次查询共涉及3张表：users、categories和category_user中间表。
当前方案通过Laravel集合的sortByDesc()方法按相关性排序：如果用户1同时关联分类1、分类2且匹配搜索关键词“my example search term”，用户2仅关联分类1，则用户1应排在搜索结果前列。
但现有排序逻辑仅统计users表字段的匹配次数，未纳入关联分类的匹配权重。

核心优化思路 #

• 提前预加载用户关联的分类数据，避免排序阶段重复查询数据库产生N+1问题
• 在权重计算逻辑中新增分类匹配的统计项，可根据业务需求设置分类匹配的权重高于用户表字段，强化分类匹配的排序优先级
• 额外修正原始查询的条件逻辑漏洞：原查询中基础筛选条件approved=1和user_type_id=1和后续的orWhere并列，会导致不符合基础条件的用户也被查询到，需要将基础条件和搜索条件整体嵌套

优化后完整代码 #

// 用户表参与权重计算的字段
$props = ['name', 'slug', 'location', 'company_name', 'biography', 'usually_active'];
// 分类匹配的权重值，可根据业务调整，设置为2代表一次分类匹配相当于两次用户表字段匹配
$categoryWeight = 2;

$search_results = User::where(function ($query) use ($search_items) {
    // 基础筛选条件放到闭包里，避免和orWhere逻辑冲突
    $query->where('approved', '=', 1)
          ->where('user_type_id', '=', 1);
          
    // 搜索用户表字段
    foreach ($search_items as $value) {
        $query->orWhere('name', 'like', "%{$value}%");
        $query->orWhere('location', 'like', "%{$value}%");
        $query->orWhere('company_name', 'like', "%{$value}%");
        $query->orWhere('biography', 'like', "%{$value}%");
        $query->orWhere('usually_active', 'like', "%{$value}%");
    }
})
->whereHas('categories', function ($q) use($search_items) {
    // 筛选关联分类匹配关键词的用户
    if(!empty($search_items)) {
        $q->whereIn('name', $search_items)
          ->orWhereIn('slug', $search_items);
    }
})
// 预加载分类数据，避免N+1查询
->with('categories')
->get()
->sortByDesc(function($user) use ($search_items, $props, $categoryWeight) {
    $weight = 0;
    // 统计用户表字段匹配权重
    foreach($search_items as $item) {
        foreach($props as $prop) { 
            if(strpos($user->{$prop}, $item) !== false) {
                $weight += 1;
            }
        }
        // 统计关联分类匹配权重
        foreach($user->categories as $category) {
            if(strpos($category->name, $item) !== false || strpos($category->slug, $item) !== false) {
                $weight += $categoryWeight;
            }
        }
    }
    return $weight;
});

// 分页处理
$search_results = $search_results->values()->all();
$search_results = $this->paginate($search_results);

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