Level 65
Hmm, should match it?
Have you tried a package like: https://github.com/TomLingham/Laravel-Searchy
Feb 25, 2016
11
Level 2
whereLike is case-sensitive?
Hi,
I have this query I'm running Eloquent on my PostgreSQL database:
$searcher = ['filled', 'array', 'list'];
$list = Product::query();
foreach($searcher as $word){
$list->where('title', 'LIKE', '%'.$word.'%');
$list->orWhere('name', 'LIKE', '%'.$word.'%');
}
$list ->orderBy('title')->get();
But I have the problem that for some reason the where Like is case-sensitive.
For example I have this in the Database: "Hello Foobar". If I search for "foobar" it doesn't find it, if I use a capital F it does.
How to fix this?
Level 54
In general for sql:
foreach($searcher as $word) {
$list->where('LOWER(title)', 'LIKE', '%' . strtolower($word) . '%');
$list->orWhere('LOWER(name)', 'LIKE', '%' . strtolower($word) . '%');
}
or with postgres you should be able to just change your 'LIKE' to 'ILIKE' (case insensitive like) -- it's important to know that ILIKE is not universally supported though.
7 likes
Level 1
For json data;
foreach($searcher as $word) {
$list->whereRaw('LOWER(JSON_EXTRACT(product_information, "$.title")) like ?', ['"%' . strtolower($word) . '%"']);
$list->orWhereRaw('LOWER(JSON_EXTRACT(product_information, "$.name")) like ?', ['"%' . strtolower($word).'%"']);
}
I needed it, I hope it will be useful to others.
1 like
Level 65
@willvincent I have a LIKE search and I can search with case-insensitive terms...
where('first_name', 'LIKE', '%'.$term.'%')
3 likes
Level 2
@willvincent thnx !! that was it :))
Level 54
@bashy - I'm not overly familiar with postgres, but maybe it depends on how the table is setup in the first place?
Level 65
@willvincent Using MySQL here. I use utf8_unicode_ci and no changes to anything really...
Just noticed we're talking about PostgreSQL here :D
Level 54
Ahh yeah, I think in mysql like's are case insensitive by default. :)
Level 17
You also can use ILIKE instead of LIKE
$list->where('title', 'ILIKE', '%' . $word . '%');
1 like
Level 1
@hamedghaderi Thank you!
Level 6
I know this is old but maybe it helps someone:
In Mysql you can use 'like' for case sensitive and 'LIKE' for not case sensitive. Not sure why this works because i cant find any documentation on it.
4 likes
Please or to participate in this conversation.