Laravel Eloquent Ignore Casing

Question

I am trying to run a query using Eloquent the $vars['language'] is in lower case but the language column is not necessarily in lower case. How can i do this search using eloquent but still have the lower case in the query

Item::where('language', $vars['language'])

What i want to do is this even though i can't find anywhere how to do this

Item::where('LOWER(language)', $vars['language'])

so that they are both in lowercase and then i can get them to match.

Use DB:raw() to use LOWER() – chris342423 Jan 19 '14 at 09:14 — chris342423, Jan 19 '14 at 09:14

Margus Pala · Accepted Answer · 2019-06-09T09:25:35.837

60

Use whereRaw with parameter binding to sanitize your whereRaw statement:

$term = strtolower($vars['language']);
Item::whereRaw('lower(language) like (?)',["%{$term}%"])->get();

Prev answer In some dabases you can use operator ilike in your where. For example

Item::where('language', 'ilike', $vars['language'])->get();

All available operators are:

protected $operators = array(
    '=', '<', '>', '<=', '>=', '<>', '!=',
    'like', 'not like', 'between', 'ilike',
    '&', '|', '^', '<<', '>>',
);

Edit: ilike is case-insensitive like.

edited Jun 09 '19 at 09:25

answered Jan 19 '14 at 12:09

Margus Pala

7,848
6
36
50

hey what is ilike and what does it do? – Lpc_dark Jan 21 '14 at 06:50
12

ilike is postgres case insensitive like – Margus Pala Jan 21 '14 at 17:27
so you mean `like` is a case insensitive operator? – alex Jun 12 '16 at 15:04
1

what does like has to do with the case sensitivity? – Sizzling Code Oct 27 '17 at 09:31
1

Where did you get the list of operators from? `regex` should be included as well. – Genti Saliu Jan 05 '18 at 20:32
nice to see you just copied my `whereRaw` answer into yours, keeping the `like` idea that doesn't even have much to do with what was asked. – igorsantos07 Jan 15 '19 at 06:20

score 18 · Answer 2 · answered Nov 09 '15 at 20:56

18

As per this post, a db-independent way to solve this would be:

Item::whereRaw('LOWER(language) = ?', [$vars['language']])->get();

answered Nov 09 '15 at 20:56

igorsantos07

4,149
4
40
59

Patrick.SE · Answer 3 · 2017-02-27T17:17:45.017

-1

The filter() collection method can be useful if you are trying to find a laravel-based solution :

// $this refers to an instance of an eloquent User Model
$name = 'BiLbo baGGings';    
return $this->contactInformation->filter(function($value, $key) use($name) { 
   if(strtolower($value->name) == strtolower($name)) {
       return $value;
   }
 });

edited Feb 27 '17 at 17:17

answered Dec 15 '16 at 14:16

Patrick.SE

4,207
5
31
44

1

This is not eagler-loading! where is the 'with' method? With $this->contactInformation you are loading to memory ALL the contacts infos, and then filtering. Hitting against memory instead of doing at a DB level is always the worst option :/ – dani24 Feb 23 '17 at 14:37
1

You are right, I edited out the parts that were incorrect about my answer to avoid confusing other readers. – Patrick.SE Feb 27 '17 at 17:18

Laravel Eloquent Ignore Casing

3 Answers3

Linked