Ole's avatar
Ole
OP
9 years ago
Level 1

Eloquent search LIKE with underscores wrong results

Hi, i have been searching for a long time now to resolve this, but i failed.... I want to realize a search by string, searching on specific columns in the database.

$response = \App\Migration::where('migration', 'LIKE', '%' . $search . '%')
                ->orWhere('batch', 'LIKE', '%' . $search . '%')
                ->paginate($pageSize);
        $response->appends(['search' => $search])->links();

This does work, so far the search string does not contain any underscores (_). But the migration names do contain a lot of underscores...so i tried to send the same query via \DB::raw('select * from migrations where migration LIKE '%$search%');. But this does not work either... When i execute a native query like SELECT * FROM migrations WHERE migration LIKE '%2016_%' everything works fine. I also tried to escape the underscores with no success...how can i solve this?

greetings from germany, Ole

0 likes
7 replies
jlrdw's avatar

It should work if a regular query works, this is a possible issue, check the issues and see if it's been brought up.

rotaercz's avatar

Having the same issue in Laravel 5.3. Any work around or has it been resolved in 5.4?

jlrdw's avatar

@rotaercz see if a regular query using getPdo() works. Then if it does could be an issue.

henriqueweiand's avatar

I'm having the same problem, when I use eloquent and in my rule ilike owns anderline it simply ignores it.

joykevin's avatar

Old question, I know. But I just stumbled accross this and might have a solution. (Laravel 8) Underscores need to be "escaped" to work.

->orWhere('batch', 'LIKE', '%\_' . $search . '\_%')

In this example i added an underscore before and after my search term. This solved the problem for me.

Artwork's avatar

@joykevin , it may not work if search would include the \ or alike. Have you considered something like the following?

use Illuminate\Database\Eloquent\Builder;

// ...

/**
 * Make a "WHERE... LIKE" database query with special characters `%` and `_` escaped.
 *
 * @see https://dev.mysql.com/doc/refman/8.0/en/string-comparison-functions.html#operator_like
 */
Builder::macro('whereLikePlain', function ($column, $value) {
    do {
        $value = preg_replace('/([^\\\\]|^)([%_])/', '$1\\\\$2', $value, -1, $c);
    } while ($c > 0);

    return $this->where($column, 'LIKE', "%$value%");
});

Please or to participate in this conversation.