Laravel Concat to get first name + middle name + last name

user_15563338 0
Viewed 2960

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.

laravel-query-builder laravel datatables php
6 Answers
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.

user_140520880

You can use CONCAT_WS function of mysql like this and also there are various way to search, so include that as well:

$query->Where(function($query) use ($s)
{
    $query->orwhere('first_name', 'like', '%'.$s.'%')
    ->orWhere('middle_name', 'like', '%'.$s.'%')
    ->orWhere('last_name', 'like', '%'.$s.'%')
    ->orWhere('email', 'like', '%'.$s.'%')
    ->orWhere('cellphone', 'like', '%'.$s.'%')
    ->orWhereRaw('CONCAT_WS(" ", trim(FirstName), trim(LastName)) like "%' . $s . '%"');
});
user_73885200

Thank you for all the inputs here. I really appreciate it. I'm actually new to laravel :) have a nice day..

Here's my final code by the way, perhaps someone will come searching for the answer one day. full filter user controller

user_155633380

Keep in mind that users might search for names in various ways:

$query->Where(function($query) use ($s)
    {
    $query->where(DB::raw("CONCAT('first_name', ' ', 'middle_name')"), 'like', '%'.$s.'%')
    ->orWhere(DB::raw("CONCAT('first_name', ' ', 'last_name')"), 'like', '%'.$s.'%')
    ->orWhere(DB::raw("CONCAT('last_name', ', ', 'first_name')"), 'like', '%'.$s.'%')
    ->orWhere(DB::raw("CONCAT('first_name', ' ', 'middle_name', ' ', 'last_name')"), 'like', '%'.$s.'%')
    ->orWhere(DB::raw("CONCAT('last_name', ', ', '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.'%');
    });
user_74511090

Try this:

 collect(explode($terms, ' '))->filter()->each(function ($term) use ($query) {
   $term = '%'.$term.'%';
   $query->where(function ($query) use ($term) {
     $query->where('first_name', 'like', $term)
           ->orWhere('last_name', 'like', $term)
           ->orWhere('..//whatever0', 'like', $term)
     });
   });

It will not use any indexes but it was not your question anyways :-)

user_68734070

This is what it worked for me:

$query->where(function ($query) use ($name) {
    $query->orWhereRaw('LOWER(first_name) like LOWER(?)', ["%{$name}%"])
        ->orWhereRaw('LOWER(middle_name) like LOWER(?)', ["%{$name}%"])
        ->orWhereRaw('LOWER(last_name) like LOWER(?)', ["%{$name}%"])
        ->orWhereRaw('LOWER(CONCAT(first_name, \' \', middle_name, \' \', last_name)) LIKE LOWER(?)', ["%{$name}%"]);
});
user_37011020
Related