如何在Laravel Eloquent中实现多列MATCH AGAINST分组逻辑查询

我需要构建一个基于MATCH AGAINST的多列搜索查询，要求每列至少匹配一个搜索短语——也就是每列的多个匹配条件用OR分组，列与列之间用AND连接。但现有代码生成的SQL把所有条件都用OR拼接，不符合需求。

现有代码 #

public function searchLogicForAutosuggestion($searchQuery, &$query, $columns)
{
    if (!is_null($searchQuery)) {
        $combinations = $this->generateSearchCombinations($searchQuery);
        if (count($combinations) > 0) {
            $query->where(function ($query) use ($columns, $searchQuery, $combinations) {
                foreach ($columns as $index => $column) {
                    if ($index == 0) {
                        foreach ($combinations as $combIndex => $combination) {
                            if ($combIndex == 0) {
                                $query->WhereRaw("match(`$column`) against( '"$combination"')");
                            } else {
                                $query->orWhereRaw("match(`$column`) against( '"$combination"')");
                            }
                        }
                    } else {
                        foreach ($combinations as $combIndex => $combination) {
                            $query->orWhereRaw("match(`$column`) against( '"$combination"')");
                        }
                    }
                }
            });
        }
    }
}

// 调用代码
$query = Model::where('is_active', 1);
if (!empty($request->search_term)) {
    $query->where(function ($q) use ($request) {
        $this->searchLogicForAutosuggestion($request->search_term, $q, ['column1', 'column2', 'column3']);
    });
}

当前生成的SQL #

SELECT 
    `id`, `name`
FROM
    `tablename`
WHERE
    `is_active` = 1
        AND ((MATCH (`column1`) AGAINST ('"ABC-228"' )
        OR MATCH (`column1`) AGAINST ('"ABC&228"' )
        OR MATCH (`column1`) AGAINST ('"ABC%228"' )
        OR MATCH (`column1`) AGAINST ('"ABC/228"' )
        OR MATCH (`column1`) AGAINST ('"ABC 228"' )
        OR MATCH (`column1`) AGAINST ('"ABC228"' )
        OR MATCH (`column2`) AGAINST ('"ABC-228"' )
        OR MATCH (`column2`) AGAINST ('"ABC&228"' )
        OR MATCH (`column2`) AGAINST ('"ABC%228"' )
        OR MATCH (`column2`) AGAINST ('"ABC/228"' )
        OR MATCH (`column2`) AGAINST ('"ABC 228"' )
        OR MATCH (`column2`) AGAINST ('"ABC228"' )))
GROUP BY `name`
LIMIT 200 OFFSET 0

期望生成的SQL #

SELECT 
    `id`, `name`
FROM
    `tablename`
WHERE
    `is_active` = 1
        AND (
           ( MATCH (`column1`) AGAINST ('"ABC-228"' )
            OR MATCH (`column1`) AGAINST ('"ABC&228"' )
            OR MATCH (`column1`) AGAINST ('"ABC%228"' )
            OR MATCH (`column1`) AGAINST ('"ABC/228"' )
            OR MATCH (`column1`) AGAINST ('"ABC 228"' )
            OR MATCH (`column1`) AGAINST ('"ABC228"' ))
        AND
            (MATCH (`column2`) AGAINST ('"ABC-228"' )
            OR MATCH (`column2`) AGAINST ('"ABC&228"' )
            OR MATCH (`column2`) AGAINST ('"ABC%228"' )
            OR MATCH (`column2`) AGAINST ('"ABC/228"' )
            OR MATCH (`column2`) AGAINST ('"ABC 228"' )
            OR MATCH (`column2`) AGAINST ('"ABC228"' ))
        )
GROUP BY `name`
LIMIT 200 OFFSET 0

修改后的代码 #

核心思路是给每个列的匹配条件单独套一层闭包，让每个列的多个OR条件形成独立分组，列之间默认用AND连接：

public function searchLogicForAutosuggestion($searchQuery, &$query, $columns)
{
    if (!is_null($searchQuery)) {
        $combinations = $this->generateSearchCombinations($searchQuery);
        if (count($combinations) > 0) {
            $query->where(function ($query) use ($columns, $combinations) {
                foreach ($columns as $column) {
                    // 每个列单独用闭包包住，内部用OR连接多个匹配条件
                    $query->where(function ($subQuery) use ($column, $combinations) {
                        foreach ($combinations as $combIndex => $combination) {
                            if ($combIndex == 0) {
                                $subQuery->whereRaw("match(`$column`) against( '"$combination"')");
                            } else {
                                $subQuery->orWhereRaw("match(`$column`) against( '"$combination"')");
                            }
                        }
                    });
                }
            });
        }
    }
}

// 调用代码保持不变
$query = Model::where('is_active', 1);
if (!empty($request->search_term)) {
    $query->where(function ($q) use ($request) {
        $this->searchLogicForAutosuggestion($request->search_term, $q, ['column1', 'column2', 'column3']);
    });
}

代码说明 #

1. 遍历每个列时，创建子查询闭包$subQuery，而非直接往主查询添加条件
2. 子查询闭包内，用OR连接当前列的所有匹配条件，形成独立分组
3. 主查询对每个列的子查询闭包使用where()，默认用AND连接所有列的分组条件，正好满足“每列至少匹配一个短语”的需求

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