I'm following this guide to get a livewire live search working on my site. Everything is working great until I hit a space (or a *) in my search field. When that happens, I get the error:
Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1064 syntax error, unexpected $end, expecting FTS_TERM or FTS_NUMB or '*'
SELECT count(*) AS aggregate FROM `users` WHERE MATCH (name) AGAINST (+john* +* IN BOOLEAN MODE)
I believe the issue lies in the following file but I can't figure out how to fix it.
Model\Search.php:
<?php
namespace App\Models;
trait Search
{
private function buildWildCards($term) {
if ($term == "") {
return $term;
}
// Strip MySQL reserved symbols
$reservedSymbols = ['-', '+', '<', '>', '@', '(', ')', '~'];
$term = str_replace($reservedSymbols, '', $term);
$words = explode(' ', $term);
foreach($words as $idx => $word) {
// Add operators so we can leverage the boolean mode of
// fulltext indices.
$words[$idx] = "+" . $word . "*";
}
$term = implode(' ', $words);
return $term;
}
protected function scopeSearch($query, $term) {
$columns = implode(',', $this->searchable);
// Boolean mode allows us to match john* for words starting with john
// (https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html)
$query->whereRaw(
"MATCH ({$columns}) AGAINST (? IN BOOLEAN MODE)",
$this->buildWildCards($term)
);
return $query;
}
}
Any ideas?
I believe I fixed the issue (unless it introduces some other issue).
I just added an if statement in the following section:
$words = explode(' ', $term);
foreach($words as $idx => $word) {
// Add operators so we can leverage the boolean mode of
// fulltext indices.
if($word != ""){
$words[$idx] = "+" . $word . "*";
}
}
This way it doesn't do a search with a wildcard only as the second term. Seems to have fixed it.