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.

Subscribe