How to search by full name if first name and last name is in different columns?

Good day everyone i have being trying to run a query that will select first_name and last_name as full_name as it is in my Model as an Accessor

This is my Agent Controller

   public function index()
    {
        $agents = Agent::with('category')
            ->when(request('search_category'), function($query) {
                $query->where('category_id', request('search_category'));
            })
            ->when(request('search_agent'), function($query) {
                $query->where('full_name', 'like', '%'.request('search_agent'). '%');
            })
            ->paginate(10);

        return AgentResource::collection($agents);
    }

This is my Model

 public function getFullNameAttribute()
    {
        return "{$this->first_name} {$this->last_name}";
    }

LaryAI

3 years ago

Level 58

To search by full name when the first name and last name are in different columns, you can concatenate the first name and last name columns in the query using the CONCAT function. Here's an example:

public function index()
{
    $agents = Agent::with('category')
        ->when(request('search_category'), function($query) {
            $query->where('category_id', request('search_category'));
        })
        ->when(request('search_agent'), function($query) {
            $query->whereRaw("CONCAT(first_name, ' ', last_name) LIKE ?", ['%'.request('search_agent'). '%']);
        })
        ->paginate(10);

    return AgentResource::collection($agents);
}

In this example, we're using the whereRaw method to add a raw SQL expression to the query. The CONCAT function concatenates the first_name and last_name columns with a space in between. We're also using a parameterized query to prevent SQL injection attacks.

Note that this solution assumes that the first_name and last_name columns are both strings. If they are not, you may need to cast them to strings before concatenating them.

Please or to participate in this conversation.