Laravel(MySQL5.7)搜索如何按匹配优先级优先返回最相关结果

核心实现逻辑是通过SQL的CASE语句给不同匹配规则的结果计算权重值，最终按权重倒序排列，完全匹配你需要的三级优先级规则，同时修复原有代码未连表、OR条件易冲突的潜在问题。

优先级权重对应规则 #

• 3分（最高优先级）：单词字段小写后与搜索词完全相等
• 2分（第二优先级）：单词字段小写后模糊匹配搜索词（非完全相等）
• 1分（第三优先级）：仅释义、例句字段匹配到搜索词

可直接使用的调整后代码 #

public function scopeWhereLike(Builder $query, string $searchString): Builder
{
    $lowercasedSearchString = Str::lower($searchString);

    return $query->leftJoin('word_definitions', 'words.id', '=', 'word_definitions.word_id')
        // 所有OR匹配条件嵌套，避免和其他查询条件逻辑冲突
        ->where(function (Builder $matchQuery) use ($lowercasedSearchString) {
            $matchQuery->whereRaw('LOWER(word_definitions.definition) LIKE ?', ["%{$lowercasedSearchString}%"])
                ->orWhereRaw('LOWER(word_definitions.example_sentence) LIKE ?', ["%{$lowercasedSearchString}%"])
                ->orWhereRaw('LOWER(words.word) LIKE ?', ["%{$lowercasedSearchString}%"]);
        })
        // 按匹配权重倒序
        ->orderByRaw("
            CASE
                WHEN LOWER(words.word) = ? THEN 3
                WHEN LOWER(words.word) LIKE ? THEN 2
                ELSE 1
            END DESC
        ", [$lowercasedSearchString, "%{$lowercasedSearchString}%"])
        // 同权重下可自定义补充排序，比如按词条热度、创建时间排序
        ->orderBy('words.word', 'asc')
        ->select('words.*')
        ->distinct();
}

注意事项 #

• 所有匹配判断统一使用数据库端LOWER()函数做大小写转换，比PHP端提前转字符串兼容性更好，避免数据库字段大小写敏感规则导致漏匹配
• 统一使用参数绑定方式传入搜索词，避免直接拼接SQL带来的注入风险
• 追加distinct()和指定select('words.*')是为了避免一个单词对应多条释义时，返回重复的单词词条
• 如果需要更细的排序粒度，比如单词前缀匹配比中间包含匹配优先级更高，可以在CASE语句中新增规则，例如给LOWER(words.word) LIKE ?（传参"{$lowercasedSearchString}%"）设置2.5的权重，会排在普通单词包含匹配之前，更符合用户搜索习惯。

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