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

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.

phplaraveldatatableslaravel-query-builder


Solution 1:[1]

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

Solution 2:[2]

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 . '%"');
});

Solution 3:[3]

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 :-)

Solution 4:[4]

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.'%');
    });

Solution 5:[5]

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}%"]);
});

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Jin
Solution 2
Solution 3 Ali Raza
Solution 4
Solution 5 pableiros

Related Questions