Laravel中如何按匹配OR条件的数量对SQL查询结果排序？

原生SQL实现 #

你可以通过计算每条记录匹配条件的数量来实现排序，WHERE子句的短路逻辑不影响排序时的条件判断——因为排序阶段会单独对每个条件进行评估，不会触发短路。

具体做法是把每个LIKE条件转换成1（匹配）或0（不匹配），求和后得到匹配次数，最后按这个结果降序排列：

SELECT *,
    (
        IF(name LIKE '%test%', 1, 0) +
        IF(title LIKE '%test%', 1, 0) +
        IF(text LIKE '%test%', 1, 0) +
        IF(author LIKE '%test%', 1, 0)
    ) AS match_count
FROM articles
WHERE name LIKE '%test%'
   OR title LIKE '%test%'
   OR text LIKE '%test%'
   OR author LIKE '%test%'
ORDER BY match_count DESC;

如果是PostgreSQL这类不支持IF语法的数据库，换成CASE表达式即可：

SELECT *,
    (
        CASE WHEN name LIKE '%test%' THEN 1 ELSE 0 END +
        CASE WHEN title LIKE '%test%' THEN 1 ELSE 0 END +
        CASE WHEN text LIKE '%test%' THEN 1 ELSE 0 END +
        CASE WHEN author LIKE '%test%' THEN 1 ELSE 0 END
    ) AS match_count
FROM articles
WHERE name LIKE '%test%'
   OR title LIKE '%test%'
   OR text LIKE '%test%'
   OR author LIKE '%test%'
ORDER BY match_count DESC;

Laravel框架实现 #

用Laravel的Query Builder或Eloquent可以轻松实现这个逻辑，核心同样是构建match_count字段后排序：

方法一：Query Builder 原生写法 #

$searchTerm = 'test';
$articles = DB::table('articles')
    ->selectRaw("*,
        (
            IF(name LIKE ? , 1, 0) +
            IF(title LIKE ? , 1, 0) +
            IF(text LIKE ? , 1, 0) +
            IF(author LIKE ? , 1, 0)
        ) AS match_count",
        ["%{$searchTerm}%", "%{$searchTerm}%", "%{$searchTerm}%", "%{$searchTerm}%"]
    )
    ->where(function ($query) use ($searchTerm) {
        $query->where('name', 'LIKE', "%{$searchTerm}%")
              ->orWhere('title', 'LIKE', "%{$searchTerm}%")
              ->orWhere('text', 'LIKE', "%{$searchTerm}%")
              ->orWhere('author', 'LIKE', "%{$searchTerm}%");
    })
    ->orderByRaw('match_count DESC')
    ->get();

方法二：Eloquent 模型写法 #

如果你用Eloquent模型，可使用更优雅的addSelect方式：

use App\Models\Article;

$searchTerm = 'test';
$articles = Article::query()
    ->addSelect([
        'match_count' => function ($query) use ($searchTerm) {
            $query->selectRaw("
                IF(name LIKE ? , 1, 0) +
                IF(title LIKE ? , 1, 0) +
                IF(text LIKE ? , 1, 0) +
                IF(author LIKE ? , 1, 0)
            ", ["%{$searchTerm}%", "%{$searchTerm}%", "%{$searchTerm}%", "%{$searchTerm}%"]);
        }
    ])
    ->where(function ($query) use ($searchTerm) {
        $query->where('name', 'LIKE', "%{$searchTerm}%")
              ->orWhere('title', 'LIKE', "%{$searchTerm}%")
              ->orWhere('text', 'LIKE', "%{$searchTerm}%")
              ->orWhere('author', 'LIKE', "%{$searchTerm}%");
    })
    ->orderBy('match_count', 'desc')
    ->get();

注意：如果项目用PostgreSQL，把代码中的IF替换成CASE WHEN ... THEN 1 ELSE 0 END即可适配。

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