How can I solve case-sensitive where like statement on json data in Laravel eloquent?

I use Laravel 5.6 and mysql database.

My Laravel eloquent query is like this:

Order::where('information->owner', 'like', '%'.$receipt.'%')->paginate(5);

Information field is a JSON type.

{"owner": "Chelsea"}

If $receipt is Chel, the query returns a result.

But if $receipt is chel or CHEL, the result is empty

How can I solve this problem?

Level 75

Use strtolower on both sides of like to make the search lowercase only.

Level 2

@jlrdw I need a spesific answer. Because I had try it. But it's the same

Level 2

@jlrdw I try this : Order::whereRaw('LOWER(information->"$.owner") like "%?%"', strtolower($receipt)) ->paginate(5);. But it's the same

1 like

Level 75

I don't understand this

where like statement on json data

Just query your mysql database first no json and search is case insensitive, at least should be.

Level 2

@MOSES - Hi Moses,

If it's not too late, try this : Order::whereRaw('LOWER(information->"$.owner") like ?', '%'.strtolower($receipt).'%'))->paginate(5);

Edit, sorry, I just see the date of this post was 2018, not 2019... :)

4 likes

Level 1

Order::whereRaw('LOWER(information->"$.owner") like ?', '%'.strtolower($receipt).'%'))->paginate(5);

This worked for me

Thanks !!!

2 likes

Please or to participate in this conversation.