Laravel JSON where clause case insensitive

Question

I am using L5.2 and MySQL 5.7. I have the following JSON on my meta column in the DB:

{"cellphone": "(687) 638-4512 x22934", "last_name": "Bashirian", "first_name": "Fidel"}

I am trying to get the following query to run whether the string is uppercase or lowercase:

User::where('meta->first_name', 'fidel');

I have tried something like:

User::whereRaw('LOWER(meta->"$.first_name") = ?', ['fidel']);

But that is returning null. Any idea on how to do this!?

---

I also wanted to be able to do queries by fullname for example 'fidel bashirian'. I tried with concat() but I am not sure if that works with JSON column types, so I ended up making it work with the following function:

protected function name($name)
{
    $name = explode(' ', $name);

    foreach ($name as $key => $value) {

        $name[$key] = '%' . strtolower($value) . '%';

        $this->builder->where(function ($query) use ($name, $key) {
            $query->whereRaw('LOWER(meta->"$.first_name") like ?', [$name[$key]]);
            $query->orWhereRaw('LOWER(meta->"$.last_name") like ?', [$name[$key]]);
        });
    }

    return $this->builder;
}

With the previous method querying by fidel, Fidel, FIDEL, fidel bashirian, etc.. will all yield results. Even with partial first or last names.

Answer 1

I solved it by using this method. With this I am able to do queries by fullname (first + ' ' + last) or by first or last names. I am not sure if this is the most elegant solution thought.

protected function name($name)
{
    $name = explode(' ', $name);

    foreach ($name as $key => $value) {

        $name[$key] = '%' . strtolower($value) . '%';

        $this->builder->where(function ($query) use ($name, $key) {
            $query->whereRaw('LOWER(meta->"$.first_name") like ?', [$name[$key]]);
            $query->orWhereRaw('LOWER(meta->"$.last_name") like ?', [$name[$key]]);
        });
    }

    return $this->builder;
}

With the previous method querying by fidel, Fidel, FIDEL, fidel bashirian, etc.. will all yield results. Even with partial first or last names.