php - 合并两个 MySQL 搜索查询

Question

我有以下方法来创建一个搜索查询，按出现次数对搜索词进行评分:

public function findAll($search, array $data = []) {
    $query = DB::query("
        SELECT 
        SQL_CALC_FOUND_ROWS 
        *, 
        SUM(MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE)) as score 
        FROM " . DB::prefix() . "search_index 
        WHERE MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE)  
        OR text LIKE '%{$search}%' 
        GROUP BY language_id, type, object_id 
        ORDER BY score DESC 
        LIMIT " . (int)$data['start'] . ", " . (int)$data['limit'] . "
    ");

    $count = DB::query("SELECT FOUND_ROWS() AS total");

    return [
        'count' => (int)$count->row['total'],
        'query' => $query->rows
    ];
}

这很好用，但我需要在 tag 表中添加一个查询，以便它会增加分数并将项目添加到数组中，以获得主查询中可能不存在的内容:

$tags = DB::query("
    SELECT * FROM " . DB::prefix() . "tag 
    WHERE tag = '{$search}'
");

关于如何将其实现到主查询中以便我只执行单个查询有什么想法吗？

Answer 1

我能够将它们与 UNION 查询结合起来:

$query = DB::query("
    (SELECT
    SQL_CALC_FOUND_ROWS 
    type, object_id, language_id, 
    SUM(MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE)) as score 
    FROM " . DB::prefix() . "search_index 
    WHERE MATCH(text) AGAINST('{$search}' IN BOOLEAN MODE)  
    OR text LIKE '%{$search}%' 
    GROUP BY language_id, type, object_id 
    ORDER BY score DESC 
    LIMIT " . (int)$data['start'] . ", " . (int)$data['limit'] . ")
    UNION DISTINCT
    (SELECT 
        section, element_id, language_id, tag 
    FROM " . DB::prefix() . "tag 
    WHERE tag = '{$search}')
");