[FIXED] Laravel Concat to get first name + middle name + last name

Issue

I'm trying to achieve a search on datatable. Right now, I'm only able to search for first name, middle name and last name etc. However, when searching for the full name (first middle and last name) the search won't work. image: success search for firtname/middle/last image2: not working when searching for fullname

In my controller, I'm using the DB::raw() concat inside a function. Here's my code:

$query->Where(function($query) use ($s)
    {
    $query->Where(DB::raw("CONCAT('first_name', ' ', 'middle_name')"), 'like', '%'.$s.'%')
    ->orwhere('first_name', 'ilike', '%'.$s.'%')
    ->orWhere('middle_name', 'ilike', '%'.$s.'%')
    ->orWhere('last_name', 'ilike', '%'.$s.'%')
    ->orWhere('email', 'ilike', '%'.$s.'%')
    ->orWhere('cellphone', 'ilike', '%'.$s.'%');
    });

Please share any fix or insights on this. Thank you in advance.

Solution

User::where(function($query) use ($input) {
            $query->orWhere(DB::raw('CONCAT(first_name, " ", middle_name," ",last_name)'), 'LIKE', '%' . $input['search'] . '%')
                    ->orWhere('first_name', 'LIKE', "%{$input['search']}%")
                    ->orWhere('middle_name', 'LIKE', "%{$input['search']}%")
                    ->orWhere('middle_name', 'LIKE', "%{$input['search']}%")
                    ->orWhere('last_name', 'LIKE', "%{$input['search']}%")
                    ->orWhere('email', 'LIKE', "%{$input['search']}%")
                    ->orWhere('cellphone', 'LIKE', "%{$input['search']}%");
        });

I hope it would work for you.

Answered By - Ankita Dobariya