CODEWITHSUNDEEP
phpmysqllaravel
Jared Eitnier
Jared Eitnier

Reputation: 7152

Laravel raw FULLTEXT query similar to Facebook Search

I am building an AJAX search feature similar to Facebook in Laravel 4.1. The search field should search the users table against first_name, last_name and email.

I might enter any of the following terms or portions of the terms such as:

First I tried:

User::where('email', 'LIKE', $search)
  ->orWhere('first_name', 'LIKE', $search)
  ->orWhere('last_name', 'LIKE', $search)
  ->get();

But that just gets a match on one of the words. Then I tried to explode the input by a space and build a query like this:

User::whereRaw("MATCH (first_name, last_name, email)
                AGAINST ('$explode[0]' IN BOOLEAN MODE)
                AND MATCH (first_name, last_name, email)
                AGAINST ('$explode[1]' IN BOOLEAN MODE)")
     ->get();

which works but only gets exact matches ('jared', 'eitnier', or 'jared eitnier')

What do I need to change to get LIKE matches but on any combination of terms entered?

Upvotes: 3

Views: 2278

Answers (1)

Jared Eitnier
Jared Eitnier

Reputation: 7152

I was able to solve this with the help of this post. Here's the final code:

$input = Input::get('input');

$exp = explode(' ', $input);

$s = '';
$c = 1;
foreach ($exp AS $e)
{
    $s .= "+$e*";

    if ($c + 1 == count($exp))
        $s .= ' ';

    $c++;
}

$query = "MATCH (first_name, last_name, email) AGAINST ('$s' IN BOOLEAN MODE)";
// $query looks like 
// MATCH (first_name, last_name, email) AGAINST ('+jar* +eitni*' IN BOOLEAN MODE)

$users = User::whereRaw($query)->get();

Upvotes: 5

Related Questions