How to search incasesensitive query in the Postgresql database using Laravel query builder?

Question

I want to create a filter. But I am facing case-sensitive problem. I have tried with my below code. But It didn't work. I have got error 'call undfiend lcase'.

 $type = strtolower($type);
 $name = strtolower($name);
 $users  = DB::table('daftar.bank_list AS uc')
     ->leftJoin('users.users as u','u.bank_id','=','uc.id')
     ->when($type, function ($query, $type) {
         return $query->where('LCASE'('uc.type'), $type);
     })
     ->when($name, function ($query, $name) {
         return $query->where('LCASE'('uc.name'), $name);
     })
     ->get();

My database is Postgresql.

Surender Singh Rawat · Accepted Answer · 2020-01-06 08:10:54Z

1

Try this:

$type = strtolower($type);
$name = strtolower($name);
$users  = DB::table('daftar.bank_list AS uc')
    ->leftJoin('users.users as u','u.bank_id','=','uc.id')
    ->when($type, function ($query, $type) {
        return $query->where('uc.type','ILIKE', '%'.$type.'%');
    })
    ->when($name, function ($query, $name) {
        return $query->where('uc.name','ILIKE', '%'.$name.'%');
    })
    ->get();

You can search by ILIKE query.

edited Jan 6, 2020 at 8:10

Surender Singh Rawat

9294 silver badges14 bronze badges

answered Jan 6, 2020 at 5:36

Kamlesh Paul

12.5k2 gold badges23 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tim Biegeleisen Over a year ago

Note that your ILIKE comparison is not the same thing as the equality expression in the OP. You may want to remove the % wildcards.

Kamlesh Paul Over a year ago

checkout this stackoverflow.com/questions/29888874/…

Tim Biegeleisen · Accepted Answer · 2020-01-06 05:35:26Z

1

Postgres uses the LOWER() function, not LCASE. Also, if you want to call a native Postgres function, you should be using whereRaw:

$users = DB::table('daftar.bank_list AS uc')
    ->leftJoin('users.users as u', 'u.bank_id', '=', 'uc.id')
    ->when($type, function ($query, $type) {
        return $query->whereRaw('LOWER(uc.type) = ?', [$type]);
    })
    ->when($name, function ($query, $name) {
        return $query->whereRaw('LOWER(uc.name) = ?', [$name]
    })
    ->get();

answered Jan 6, 2020 at 5:35

Tim Biegeleisen

526k32 gold badges323 silver badges399 bronze badges

Collectives™ on Stack Overflow

How to search incasesensitive query in the Postgresql database using Laravel query builder?

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related