Laravel Eloquent - Get nested relationships with filtered data-CodePudding

suppose I have the following models:

Categories-(hasMany/belongsTo)-Subcategories-(hasMany/belongsTo)-Products

These models create a collapsible product list in the frontend, like so:

Category1
  - Subcategory1
    - Product1
    - Product2
Category2
  - SubCategory3
    - Product4

Now, I want to search for Product1 and retrieve it while keeping the relationship Category->Subcategory->Product intact, so that I can easily print it out like so:

Category1
 - Subcategory1
  - Product1

I know that with the following query you can search all categories that have a product with a certain condition:

<?php
    $Categories = Categories::whereHas('subcategories', function ($q) use ($request) {
        $q->whereHas('products', function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
              ->orWhere('article_number', 'LIKE', "%$request->search%");
        });
    })
    ->get();
?>

But that only returns the categories.

I was then going to turn them into a collection with the following resource class, before I learned I actually only get the categories from the query above:

$Collection = CategoriesResource::collection($Categories);

//------------------------------------------------

class CategoriesResource extends JsonResource
{
    public function toArray($request)
    {
        return [
            'id' => $this->id,
            'name' => $this->name,
            'subcategories' => $this->subcategories()->with('products')->get(),
        ];
    }
}

My question is, can I either use a different query beforehand, or pass a condition to the with-statement, so that I will only get products that meet the search condition? Ideally, I'd want to have no empty categories or subcategories in the dataset, but if it's not manageable otherwise, it'd be ok.

I also tried it in the reverse by searching through products directly and get the categories with the belongsTo relationship, but I don't know a feasible way to reverse Product->Categories back to Categories->Products.

Edit: OMR's solution helped me, but I had to add in one other whereHas-query to filter out empty subcategories.

    $Categories = Categories::whereHas('subcategories', function ($q) use ($request) {
        $q->whereHas('products', function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        });
    })->with(['subcategories'=> function ($q) use ($request) {
        $q->whereHas('products', function ($q) use($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        })->with(['products'=>function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        }]);
    }])->get();

CodePudding user response：

you can repeat the same condition on eager loading:

  $Categories = Categories::whereHas('subcategories', function ($q) use ($request) {
        $q->whereHas('products', function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        });
    })->with(['subcategories'=> function ($q) use ($request) {
        $q->with(['products'=>function ($q) use ($request) {
            $q->where('name', 'LIKE', "%$request->search%")
                ->orWhere('article_number', 'LIKE', "%$request->search%");
        }]);
    }])->get();