Use inverse LIKE in laravel eloquent-CodePudding

Hey guys this is my first question here Basically I would like to achieve this select in LARAVEL Eloquent SELECT * FROM `precio_canales` WHERE JHON SMITH PORKI' LIKE CONCAT('%',precio_canales.nombre_cliente,'%') So for example if I receive "JHON SMITH PORKI" as input I expect that the database returns all querys with the name "JHON SMITH"

I have tried:

$precio_canal = DB::table('precio_canales')->where('Jhon Smith porki', 'LIKE', DB::raw(`CONCAT("%",precio_canales.nombre_cliente,"%"`))->get(); and also:

$precio_canal = PrecioCanales::where('Jhon smith Porki ' , 'LIKE' , DB::raw('concat("%",nombre_cliente,"%")'))->get();

Both querys return a 500 internnal server error Any help would be much appreciated! thanks in advance

CodePudding user response：

where expects a column. You need to pass a raw expression in your query.

Try either

$precio_canal = DB::table('precio_canales')
    ->whereRaw('"Jhon Smith porki" LIKE CONCAT("%", precio_canales.nombre_cliente, "%")')
    ->get();

$precio_canal = DB::table('precio_canales')
    ->whereRaw('"Jhon Smith porki" LIKE "%" || precio_canales.nombre_cliente || "%"')
    ->get();

You should also escape the user's input. You can do that by replacing it with a ? and then passing the value in an array as the second parameter

$precio_canal = DB::table('precio_canales')
    ->whereRaw('"?" LIKE CONCAT("%", precio_canales.nombre_cliente, "%")', ['Jhon Smith porki'])
    ->get();

$precio_canal = DB::table('precio_canales')
    ->whereRaw('"?" LIKE "%" || precio_canales.nombre_cliente || "%"', ['Jhon Smith porki'])
    ->get();

CodePudding user response：

You should look at laravel eloquent docs

In where method, first parameter is column name, second is comparasion operator (optional) and thir is comparator value.

So your query should be:

$precio_canal = DB::table('precio_canales')
->where(DB::raw(`precio_canales.nombre_cliente`), 'LIKE', '%Jhon Smith porki%')
->get();