Laravel与MySQL多列高级搜索：组合词层级匹配需求

Alright, let's break down how to build this priority-based advanced search for your Laravel application. We'll cover both Eloquent (the Laravel way) and raw SQL approaches, since you mentioned either is acceptable.

Laravel Eloquent Implementation #

First, we can define a query scope in your corresponding model (let's assume it's an Article model) to make this search logic reusable across your app.

Here's the implementation that follows your priority rules:

public function scopeAdvancedSearch($query, $searchString)
{
    $words = array_filter(explode(' ', trim($searchString))); // Filter out empty strings from extra spaces
    $wordCount = count($words);

    // Only support multi-word combinations; return empty if less than 2 words
    if ($wordCount < 2) {
        return $query->whereRaw('1=0');
    }

    // 1. First priority: Match the full query string
    $fullMatchQuery = clone $query;
    $fullMatchResults = $fullMatchQuery->where(function($q) use ($searchString) {
        $q->where('title', 'LIKE', "%{$searchString}%")
          ->orWhere('short_title', 'LIKE', "%{$searchString}%")
          ->orWhere('content', 'LIKE', "%{$searchString}%")
          ->orWhere('reporter', 'LIKE', "%{$searchString}%");
    })->get();

    if ($fullMatchResults->isNotEmpty()) {
        return $fullMatchResults;
    }

    // 2. Second priority: Match the first two words
    $firstTwoWords = implode(' ', array_slice($words, 0, 2));
    $firstTwoMatchQuery = clone $query;
    $firstTwoResults = $firstTwoMatchQuery->where(function($q) use ($firstTwoWords) {
        $q->where('title', 'LIKE', "%{$firstTwoWords}%")
          ->orWhere('short_title', 'LIKE', "%{$firstTwoWords}%")
          ->orWhere('content', 'LIKE', "%{$firstTwoWords}%")
          ->orWhere('reporter', 'LIKE', "%{$firstTwoWords}%");
    })->get();

    if ($firstTwoResults->isNotEmpty()) {
        return $firstTwoResults;
    }

    // 3. Third priority: Match the last two words
    $lastTwoWords = implode(' ', array_slice($words, -2));
    return $query->where(function($q) use ($lastTwoWords) {
        $q->where('title', 'LIKE', "%{$lastTwoWords}%")
          ->orWhere('short_title', 'LIKE', "%{$lastTwoWords}%")
          ->orWhere('content', 'LIKE', "%{$lastTwoWords}%")
          ->orWhere('reporter', 'LIKE', "%{$lastTwoWords}%");
    })->get();
}

To use this in your controller, it's as simple as:

$searchResults = Article::advancedSearch('Shaikh Shamim Reza')->get();

Quick Optimization Tip #

If you're working with a large dataset, replace the LIKE clauses with full-text search for better performance. Add full-text indexes to your title, short_title, reporter, and content columns, then modify the where clauses like this:

$q->whereRaw("MATCH(title, short_title, reporter) AGAINST(? IN BOOLEAN MODE)", [$searchString])
  ->orWhereRaw("MATCH(content) AGAINST(? IN BOOLEAN MODE)", [$searchString]);

Raw SQL Implementation #

If you prefer using raw SQL, here are two practical approaches:

Approach 1: Step-by-Step Priority Check #

This runs queries in order of priority, stopping as soon as it finds results:

SET @search_string = 'Shaikh Shamim Reza';
SET @first_two = SUBSTRING_INDEX(@search_string, ' ', 2);
SET @last_two = SUBSTRING_INDEX(@search_string, ' ', -2);

-- 1. Check full string match first
SELECT * FROM your_table
WHERE title LIKE CONCAT('%', @search_string, '%')
   OR short_title LIKE CONCAT('%', @search_string, '%')
   OR content LIKE CONCAT('%', @search_string, '%')
   OR reporter LIKE CONCAT('%', @search_string, '%')
LIMIT 100; -- Adjust limit as needed

-- If no full matches, check first two words
IF ROW_COUNT() = 0 THEN
    SELECT * FROM your_table
    WHERE title LIKE CONCAT('%', @first_two, '%')
       OR short_title LIKE CONCAT('%', @first_two, '%')
       OR content LIKE CONCAT('%', @first_two, '%')
       OR reporter LIKE CONCAT('%', @first_two, '%')
    LIMIT 100;
END IF;

-- If still no matches, check last two words
IF ROW_COUNT() = 0 THEN
    SELECT * FROM your_table
    WHERE title LIKE CONCAT('%', @last_two, '%')
       OR short_title LIKE CONCAT('%', @last_two, '%')
       OR content LIKE CONCAT('%', @last_two, '%')
       OR reporter LIKE CONCAT('%', @last_two, '%')
    LIMIT 100;
END IF;

Approach 2: Single Query with Priority Sorting #

This returns all matching results sorted by priority, so you only get the highest-priority matches first:

SET @search_string = 'Shaikh Shamim Reza';
SET @first_two = SUBSTRING_INDEX(@search_string, ' ', 2);
SET @last_two = SUBSTRING_INDEX(@search_string, ' ', -2);

SELECT *,
    CASE
        WHEN (title LIKE CONCAT('%', @search_string, '%') OR short_title LIKE CONCAT('%', @search_string, '%') OR content LIKE CONCAT('%', @search_string, '%') OR reporter LIKE CONCAT('%', @search_string, '%')) THEN 1
        WHEN (title LIKE CONCAT('%', @first_two, '%') OR short_title LIKE CONCAT('%', @first_two, '%') OR content LIKE CONCAT('%', @first_two, '%') OR reporter LIKE CONCAT('%', @first_two, '%')) THEN 2
        WHEN (title LIKE CONCAT('%', @last_two, '%') OR short_title LIKE CONCAT('%', @last_two, '%') OR content LIKE CONCAT('%', @last_two, '%') OR reporter LIKE CONCAT('%', @last_two, '%')) THEN 3
        ELSE 4
    END AS priority
FROM your_table
WHERE priority IN (1,2,3)
ORDER BY priority ASC
LIMIT 100;

内容的提问来源于stack exchange，提问作者Shaikh Shamim Reza