phpmysqllaravellaravel-livewire

Laravel Livewire Search Issue With Space


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?


Solution

  • 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.