MySQL/PHP Boolean Fulltext Search Issue in Laravel 4

Question

I have the following code that searches my models in Laravel 4 for a search phrase. It uses 'IN BOOLEAN MODE' and MATCH() and AGAINST().

public function scopeSearch($query, $q) 
{
    $fields = Static::getFields();
    $fields = implode(', ', $fields);

    $query->whereRaw("MATCH(" . $fields . ") AGAINST('" . $q . "' IN BOOLEAN MODE)");
}

public static function getFields()
{
    $field_names = array();
    $disallowed = array('id', 'created_at', 'updated_at', 'deleted_at');

    $columns = DB::select('SHOW COLUMNS FROM accounts');
    foreach ($columns as $c) {
        $field = $c->Field;
        if ( ! in_array($field, $disallowed)) {
            $field_names[$field] = $field;
        }
    }

    return $field_names;
}

I'd like help modifying the code above to allow a user to search the fields using partial words and phrases. For example, if a user types purple, I'd like the search to also find any records with email addresses containing the word purple, so [email protected]. So, essentially partial matches.

I'd also like to be able to find everything containing griffon in the field for the typed phrase john griffon, even if john does not exist.

Can anyone help me out with this? Cheers.

Answer 1

OK, I've got it working as best I can with FULLTEXT search and using wildcard operators to search for partials. This may not be the best solution but it works.

public function scopeSearch($query, $q) 
{
    $fields = Static::getFields();
    $fields = implode(', ', $fields);
    $terms = explode(' ', $q);

    if (count($terms) > 1) {
        $query->whereRaw("MATCH(" . $fields . ") AGAINST ('" . $q . "' IN BOOLEAN MODE)");
    } else {
        foreach ($terms as $term) {
            $query->whereRaw("MATCH(" . $fields . ") AGAINST ('*" . $term . "*' IN BOOLEAN MODE)");
        } 
    }
}

public static function getFields()
{
    $field_names = array();
    $disallowed = array('id', 'country_id', 'created_at', 'updated_at', 'deleted_at');

    $columns = DB::select('SHOW COLUMNS FROM venues');
    foreach ($columns as $c) {
        $field = $c->Field;
        if ( ! in_array($field, $disallowed)) {
            $field_names[$field] = $field;
        }
    }

    return $field_names;
}

If anyone can either simplify or improve this, then I would love to see it. Cheers.