Laravel search with relations

Question

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

What you tried is not what I suggested though. I did not typehint Category in the whereHas closure. — Mozammil
– Mozammil, Commented Feb 4, 2019 at 9:26
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
– Stormhammer, Commented Feb 4, 2019 at 9:27
Suggestion 2 in the original post is the variant which is not typehinted. But that also does not work. — wiwa1978
– wiwa1978, Commented Feb 4, 2019 at 9:27
So you want to search items that have a Category with a specific name. Is that right? — Mozammil
– Mozammil, Commented Feb 4, 2019 at 9:30

Sergey Podgornyy · Accepted Answer · 2019-02-04 09:28:11Z

3

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

edited Feb 4, 2019 at 9:28

answered Feb 4, 2019 at 9:10

Sergey Podgornyy

72811 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

wiwa1978 Over a year ago

Hi, thanks for your answer. Your suggestion does not seem to work. I edited the original post with error messages

Sergey Podgornyy Over a year ago

Sorry, then just delete Category type-hinting from the query. I'll change my answer right now

Sergey Podgornyy Over a year ago

@wiwa1978, I also updated my answer using orWhereHas method to save first condition

wiwa1978 Over a year ago

This works indeed, thanks. I'll accept this answer and update original post

Sergey Podgornyy Over a year ago

Great! Was glad to help you

Mozammil · Accepted Answer · 2019-02-04 09:15:22Z

3

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

edited Feb 4, 2019 at 9:15

answered Feb 4, 2019 at 9:07

Mozammil

8,77017 silver badges30 bronze badges

1 Comment

wiwa1978 Over a year ago

Thanks Mozammil. This seems correct also. Similar to @Sergey answer. Note: I just used the orWhereHas instead of whereHas. Thanks for your help!

Collectives™ on Stack Overflow

Laravel search with relations

2 Answers 2

5 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related