Laravel中反向匹配用户搜索记录的查询统计问题

Your original approach is on the right track (checking if the input text contains each searched_text), but there are two key issues causing it to return 0:

• Unsafe string concatenation: Directly inserting $text into raw SQL can lead to syntax errors (if the text has quotes or special characters) and severe SQL injection risks.
• Potential case sensitivity: Depending on your database's collation, LIKE might be case-sensitive, so a stored "iPhone" wouldn't match "iphone" in the input text.

Here's a clean, safe, and reliable solution:

Basic Case-Sensitive Match #

$count = Search::whereRaw('? LIKE CONCAT("%", searched_text, "%")', [$text])->count();

Case-Insensitive Match (Recommended) #

To ensure matches regardless of uppercase/lowercase differences:

$count = Search::whereRaw('LOWER(?) LIKE CONCAT("%", LOWER(searched_text), "%")', [$text])->count();

Why This Works: #

• Parameter binding: Using [$text] as the second argument to whereRaw safely escapes the input text, avoiding syntax errors and SQL injection.
• Reverse LIKE logic: The condition checks if the input text (passed as a parameter) contains the searched_text value from each record—exactly what you need for your use case.
• Case insensitivity: Wrapping both sides in LOWER() ensures matches even when the case doesn't align between the input text and stored search records.

Testing with Your Example: #

For input text "apple iphone X 16gb" and stored searched_text values "iphone", "apple", "samsung":

• The query will match "iphone" and "apple" (since both are substrings of the input text)
• It will exclude "samsung"
• The count returned will be 2, which is your expected result.

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