CODEWITHSUNDEEP
laraveleloquent
wiwa1978
wiwa1978

Reputation: 2687

Laravel search with relations

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

Upvotes: 0

Views: 831

Answers (2)

Sergey Podgornyy
Sergey Podgornyy

Reputation: 708

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

Upvotes: 3

Mozammil
Mozammil

Reputation: 8750

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

Upvotes: 3

Related Questions