searching on name instead of id on sql

April 12, 2022

I have search input in my products page. I can search on name on description and on category if I type the id but that’s not really practical how i change this sql so i can search on the name of the category instead of the id.

DB i have Products | name,description,category_id
Categories | id,name

    public function scopeFilter($query,array $filters){
    if ($filters['search'] ?? false){
        $query
            ->where('name', 'like', '%' . request('search') . '%')
            ->orWhere('description', 'like', '%' . request('search') . '%')
            ->orWhere('category_id', 'like', '%' . request('search'). '%');
    }
}

>Solution :

Since the category is a related model search on the relationship:

public function scopeFilter($query,array $filters){
    if ($filters['search'] ?? false){
        $query
            ->where('name', 'like', '%' . request('search') . '%')
            ->orWhere('description', 'like', '%' . request('search') . '%')
            ->orWhereHas('category', function ($q) {
               $q->where('name','like', '%' . request('search'). '%'
            });
    }
}

This is assuming you have defined your relationship in your model.