Go to Home

Laravel Eloquent case insensitive like search in MySql json columns

Recently while working on one of my office projects, I stumbled upon a issue where I was receiving zero results for the like search on orders table while using Laravel's Eloquent. To explain it better, I was performing the following query

There is nothing wrong about the query I have formed here, except that I was using MySQL json columns instead of default ones. So, by enabling the laravel debugbar package, I found that query formed by Laravel is as follows

select * from `orders` where json_unquote(json_extract(`user`, '$."name"')) LIKE '%Sarav%'

The problem with the above query is, MySQL json like search is case sensitive and it doesn't work had you passed the values with different case. So basically I was passing name as "%sarav%" instead of "%Sarav%" and I was not getting any matching records. So, inorder to fix this, we need to use DB::raw to lower case the mysql data instead of normal where query, in addition to that we have to convert the params to lowercase as well.

I have shown two approaches to resolve this issue and you can follow which ever you feel like you are comfortable with.

Happy Coding!

One email per week. No spam ever.