Laravel eloquent query with filter "where ILIKE" does not match with any value with accent saved in my Postgre database (encoded in UTF8)

Question

I have a Laravel 5.8 app. It works with a Postgres Database encoded with UTF8.

I have a query for my users table, so i made my query as:

$users = User::where('name', 'ILIKE', '%Jose%')->get()

It give me back the rows who match with "Jose" but let aside rows with "José". How i can do it (get all rows, who match with "Jose" and "José")?

Answer 1

You can do it using whereRaw like this:

$users = User::whereRaw('lower(name) ILIKE ?',['%Jose%'])->get();