'Where like' clause using the concatenated value of 2 columns with eloquent

Question

I have a query that searches for a term in several columns and one of them has to be a full name.

I have separated the full name in name and lastname, so I have to concat these 2 values when searching.

I have this right now only searching for name. How I would add the concat to lastname? I was investigating over mutators but I don't know if it is the way to way.

public function search($searchQuery)
{
    $search = "%{$searchQuery}%";

    return Order::with('customer')
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where('dni', 'like', $search);
                    })
                    ->orWhereHas('customer', function($query) use ($search){
                        $query->where('name', 'like', $search);
                    })
                    ->orWhere('code', 'like', $search)
                    ->paginate(15);
}

Answer 1

Mutators are not the way to go here, because they do not affect the query you're building, they are only used on the values stored in the model. However you can use the following condition:

$query->where(DB::raw('CONCAT_WS(" ", name, lastname)'), 'like', $search);

The MySQL function CONCAT_WS will concatenate two strings, gluing them with the string passed as the first parameter. So in this case if the first name is John and the last name is Smith, the condition will be evaluated to this "John Smith" like "%query%". The DB:raw is used to avoid the first part of the condition to be escaped which would put backticks around the concatenation statement.

Answer 2

I tried @bogdan's solution but had to specify the % in front of $search

$query->where(DB::raw('CONCAT_WS(" ", name, lastname)'), 'like', '%' . $search . '%');