Laravel Eloquent case-insensitive query

Question

I want to gather all the tags' ids whose names are in an array. For that I'm using whereIn like so:

$tags = Category::whereIn('name', $tag_names)->pluck('id')->toArray();

I works perfectly but I doesn't have an option (that I know of) that makes the operator of whereIn case-insentive.

$tag_names are an array of tag names which have arbitrary casing.

'name' is the column that holds the name of the tag in the database and has specific casing that may not match the one in $tag_names.

My postgresql schema for the Categorytable is:

CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL UNIQUE,
    description TEXT,
    num_posts INTEGER DEFAULT 0 NOT NULL
);

You could try whereIn('LCASE(name)',strtolower($tag_names)) — mighty_dev
– mighty_dev, Commented May 26, 2018 at 17:58
@vivek_23 Firstly, the second argument should be array_map('strtolower', $tag_names). Secondly, the first argument throws a Postgresql error, even if I try it with lower instead of LCASE. Thank you anyway for trying to help! — Daniel Marques
– Daniel Marques, Commented May 26, 2018 at 18:28
I apologize, I need some coffee. By the way, I checked this on my machine and it does work fine. — mighty_dev
– mighty_dev, Commented May 26, 2018 at 19:07

Watercayman · Accepted Answer · 2018-05-26 18:07:54Z

2

The array in the search makes it a little more difficult. If you want a non-case-sensitive result, you can just force them both to one case:

$tags = Category::whereIn( 'LCASE(name)' ,  strtolower($tag_names) )

Alternately, if you want to match without forcing the case, you might try:

$query = Category::with('tags');
foreach ($tag_names as $tag) {
     $query->whereHas('tag', function($q) use ($tag) {
         $q->where('name', 'ilike', $tag);
     });
 }
 $query->get();

answered May 26, 2018 at 18:07

Watercayman

8,17810 gold badges35 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Marques Over a year ago

I would really like an approach like the first one but it doesn't work. Firstly, the second argument should be array_map('strtolower', $tag_names). Secondly, the first argument throws a Postgresql error, even if I try it with lower instead of LCASE. Thank you anyway for trying to help!

Jonas Staudenmeir Over a year ago

You have to use a raw expression: whereIn(DB::raw('lower(name)'),

Collectives™ on Stack Overflow

Laravel Eloquent case-insensitive query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related