CODEWITHSUNDEEP
phpmysql
secondman
secondman

Reputation: 3277

Combine Two MySQL Search Queries

I have the following method to create a search query that scores the search term by occurrence:

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
    ];
}

This works beautifully, but I need to add in a query to the tag table so that it will increment the score and add items to the array for content that may not already exist in the main query:

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

Any ideas on how to implement this into the main query so that I'm only executing a single query?

Upvotes: 2

Views: 91

Answers (1)

secondman
secondman

Reputation: 3277

I was able to combine these with a UNION query:

$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}')
");

Upvotes: 1

Related Questions