Laravel search with relations

0
votes

I have the following relations in my Laravel application

class Item extends Model 
{
    public function category()
    {
        return $this->belongsTo('App\Category');
    }
}

and

class Category extends Model
{
    public function item()
    {
        return $this->hasMany('App\Item');
    }
}

I want to implement search functionality so I have created the following Eloquent query:

$items = Item::where('item_type', '=', 'type1')
         ->where(function($query) use ($q) {
            $query->where('item_name','LIKE','%'.$q.'%')
                  ->orWhere('item_description','LIKE','%'.$q.'%');
         })
         ->paginate(10);

This is working as expected and returns the search results for 'q' based on the name and the description of the item.

As a next step, I would like to also search for the category_name. Because of the relation, I have the category_id stored in the Items table, but I would like to use the category_name in my Eloquent query.

Anyone could provide some help?

Based on feedback received, I tried:

Suggestion 1:

$items = Item::where('item_type', '=', 'type1')
         ->where(function($query) use ($q) {
           $query
             ->where('item_name', 'LIKE' ,'%'.$q.'%')
             ->orWhere('item_description', 'LIKE' ,'%'.$q.'%');
           })
           ->whereHas('category', function (Category $query) use ($q) {
              $query->where('category_name', $q);
           })

=> this gives following error message:

Argument 1 passed to App\Http\Controllers\ItemController::App\Http\Controllers{closure}() must be an instance of App\Http\Controllers\App\Category, instance of Illuminate\Database\Eloquent\Builder given

Suggestion 2:

$items = Item::where('item_type', '=', 'type1')
         ->where(function($query) use ($q) {
           $query
             ->where('item_name', 'LIKE' ,'%'.$q.'%')
             ->orWhere('item_description', 'LIKE' ,'%'.$q.'%');
           })
           ->whereHas('category', function ($query) use ($q) {
              $query->where('category_name', $q);
           })

=> this does not result any search result anymore (also not for item_name and item_description).

Solution

  $items = Item::where('item_type', '=', 'type1')
            ->where(function($query) use ($q) {
                $query
                 ->where('item_name', 'LIKE' ,'%'.$q.'%')
                 ->orWhere('item_description', 'LIKE' ,'%'.$q.'%');
              })
            ->orWhereHas('category', function ($query) use ($q) {
                 $query->where('category_name', $q);
              })
            ->sortable(['id' => 'desc'])
            ->paginate(10);
2
laraveleloquent
What you tried is not what I suggested though. I did not typehint Category in the whereHas closure. - Mozammil
Have you tried "orWhereHas" instead of "whereHas" in suggestion 2? As you don't want the result where both are equal to $q, rather than where one of them is equal to $q - Stormhammer
Suggestion 2 in the original post is the variant which is not typehinted. But that also does not work. - wiwa1978
So you want to search items that have a Category with a specific name. Is that right? - Mozammil

2 Answers

3
votes

As you already described relation to Category in your Item model, you have to use just whereHas method:

$items = Item::where('item_type', '=', 'type1')
         ->where(function($query) use ($q) {
            $query->where('item_name','LIKE','%'.$q.'%')
                  ->orWhere('item_description','LIKE','%'.$q.'%');
         })
         ->orWhereHas('category', function ($query) use ($q) {
            $query->where('category_name', 'LIKE', "%$q%");
         })
         ->paginate(10);
3
votes

You could add a whereHas and constrain it. For example:

$items = Item::where('item_type', '=', 'type1')
    ->where(function($query) use ($q) {
        $query->where('item_name','LIKE','%'.$q.'%')
            ->orWhere('item_description','LIKE','%'.$q.'%');
    })
    ->whereHas('category', function($query) {
        $query->where('category_name', 'name');
    })
    ->paginate(10);