如何在Laravel中实现多表优先级排序搜索？

针对你需要的多表优先级搜索需求，这里给出一套落地的实现步骤和代码示例：

一、预处理查询词：移除停用词 #

首先要把查询里的无效停用词过滤掉，比如示例里的“什么是”：

/**
 * 过滤查询中的停用词和标点
 * @param string $query 原始查询语句
 * @return string 清理后的查询词
 */
function filterStopWords(string $query): string
{
    // 可根据业务扩展停用词库
    $stopWords = ['什么是', '什么', '是', '的', '了', '呢'];
    // 移除问号等标点
    $query = preg_replace('/[?！。]/', '', $query);
    // 替换停用词并去重空格
    return trim(preg_replace('/\s+/', ' ', str_replace($stopWords, '', $query)));
}

// 使用示例
$originalQuery = "什么是Backend Developer？";
$cleanQuery = filterStopWords($originalQuery); // 输出：Backend Developer

二、拆分关键词与同义词配置 #

把清理后的查询词拆分成完整短语、单个关键词，同时维护同义词映射（方便匹配子分类或近义词）：

$lowerQuery = strtolower($cleanQuery);
// 拆分单个关键词（假设是双词组合，多词可调整拆分逻辑）
[$mainKeyword, $subKeyword] = explode(' ', $lowerQuery);

// 同义词/子分类映射，可存入配置文件或数据库动态维护
$synonymMap = [
    'backend' => ['后端', '服务器端', 'back-end', '后端开发'],
    'developer' => ['开发工程师', '程序员', 'dev', '研发']
];

三、多表优先级查询 #

分别对user_profile和feeds表构建查询，通过CASE语句给不同匹配规则赋予权重，权重越高排名越靠前。

1. 用户资料表（user_profile）查询 #

基于skills、interest、education三个字段搜索：

use Illuminate\Support\Facades\DB;

$userProfiles = DB::table('user_profile')
    ->select(
        'id',
        'name',
        'skills',
        'interest',
        'education',
        // 计算权重：完整短语匹配>单个关键词1>单个关键词2>同义词匹配
        DB::raw("
            CASE
                WHEN LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ? THEN 4
                WHEN LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ? THEN 3
                WHEN LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ? THEN 2
                WHEN (LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ? OR LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?) THEN 1
                ELSE 0
            END AS weight
        "),
        DB::raw("'user_profile' AS source") // 标记来源表
    )
    // 参数绑定避免SQL注入
    ->whereRaw("
        LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?
        OR LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?
        OR LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?
        OR LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?
        OR LOWER(CONCAT(skills, ' ', interest, ' ', education)) LIKE ?
    ", [
        "%{$lowerQuery}%",
        "%{$mainKeyword}%",
        "%{$subKeyword}%",
        '%' . implode('%', $synonymMap[$mainKeyword]) . '%',
        '%' . implode('%', $synonymMap[$subKeyword]) . '%'
    ])
    ->having('weight', '>', 0) // 只保留有效匹配结果
    ->get();

2. 动态信息流表（feeds）查询 #

基于title、description字段搜索，逻辑和用户表一致：

$feeds = DB::table('feeds')
    ->select(
        'id',
        'title',
        'description',
        DB::raw("
            CASE
                WHEN LOWER(CONCAT(title, ' ', description)) LIKE ? THEN 4
                WHEN LOWER(CONCAT(title, ' ', description)) LIKE ? THEN 3
                WHEN LOWER(CONCAT(title, ' ', description)) LIKE ? THEN 2
                WHEN (LOWER(CONCAT(title, ' ', description)) LIKE ? OR LOWER(CONCAT(title, ' ', description)) LIKE ?) THEN 1
                ELSE 0
            END AS weight
        "),
        DB::raw("'feeds' AS source")
    )
    ->whereRaw("
        LOWER(CONCAT(title, ' ', description)) LIKE ?
        OR LOWER(CONCAT(title, ' ', description)) LIKE ?
        OR LOWER(CONCAT(title, ' ', description)) LIKE ?
        OR LOWER(CONCAT(title, ' ', description)) LIKE ?
        OR LOWER(CONCAT(title, ' ', description)) LIKE ?
    ", [
        "%{$lowerQuery}%",
        "%{$mainKeyword}%",
        "%{$subKeyword}%",
        '%' . implode('%', $synonymMap[$mainKeyword]) . '%',
        '%' . implode('%', $synonymMap[$subKeyword]) . '%'
    ])
    ->having('weight', '>', 0)
    ->get();

四、合并结果并按优先级排序 #

把两个表的查询结果合并，按照权重降序排列，权重相同的可以根据更新时间或其他业务字段二次排序：

// 合并两个集合
$combinedResults = $userProfiles->merge($feeds);

// 按权重降序排序，权重相同则按id降序（可替换为updated_at等字段）
$sortedResults = $combinedResults
    ->sortByDesc(function ($item) {
        return [$item->weight, $item->id];
    })
    ->values(); // 重置集合索引

// 返回最终结果
return response()->json($sortedResults);

性能优化建议 #

• SQL注入防护：上面的代码已经用参数绑定替代了字符串拼接，务必坚持这种写法，避免注入风险。
• 全文索引：如果数据量较大，原生LIKE查询效率极低，建议给搜索字段创建MySQL FULLTEXT索引，或者直接使用Elasticsearch等专业搜索引擎。
• 缓存优化：高频查询的关键词可以缓存结果，减少数据库查询压力。
• 动态词库：把停用词、同义词库存入数据库或配置文件，方便运营人员动态维护，无需修改代码。

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