How to get categories and products through a brand?

Question

Laravel

Hello. I am writing a site in laravel, but I ran into a problem:

### I have:

1. Parts
2. Brands
3. Categories

# Parts have relationships:
1. belongsTo(Brands)
2. belongsToMany(PartCategory::class, 'pivot_part_categories', 'part_id', 'category_id' );

# Brands are related:
1. hasMany(Part::class)

# Categories are related:
1. belongsToMany(Part::class, 'pivot_part_categories', 'category_id', 'part_id');

The classic output of the catalog built, show all the spare parts where the brand made such and such.

# I don't understand how to make such a directory:

We click on some brand, then we show all categories of goods that belong to spare parts that have brand == selected (on which they clicked)

# Examples of what I need:



# What I have already done and how I tried to solve the issue:
--- -------------------------------------------------- ----------
My routes look like this:

Route::get('/part/{slug}', 'Parts\[email protected]')->name('part.show');
Route::get('/category/{slug}', 'Parts\[email protected]')->name('category.show');

Route::get('brands/{brand}/{category?}', ['as' => 'brands.cats', 'uses' => 'Parts\[email protected]', function($brand = null, $category = null){}]);

I wrote the following partsByBrand() method:

public function partsByBrand($brand, $category)
    {
        $brand = $this->findBrandBySlug($brand); // Получил бренд
        $parts = Part::with('categories')->where('parts.brand_id','=', $brand->id)->get(); // Получил запчасти привязанные к бренду и категорию каждой запчасти
        return $parts;
    }

I don't know where to go next.

I can create an empty array of categories, iterate through all categories in a loop (the current products and pass them to the view so that they open in this route:

Route::get('brands/{brand}/{category?}', ['as' => 'brands.cats', 'uses' => 'Parts\[email protected]', function($brand = null, $category = null){}]);

Next, check that if there is a category, I get all products where the category and brand are equal to N, but it seems to me that this is a bad decision, it confuses the number of requests to display the catalog + the correctness of this solution, + how it will work when there are more spare parts 2 million

+ I need to display several different views along the same route, when I first show the categories found, and then the products in these categories,
is this done simply through if () or is there a better solution?

Answer 1

use whereHas

Answer 2

select (*, list of fields you want) from categories join parts on categories.id=parts.cat_id join brands on parts.brand=brands.id where brand.title='title', categories.title='title' and so on .d.;

Answer 3

$parts = Part::with('categories')->where('parts.brand_id','=', $brand->id)->get(); // Получил запчасти привязанные к бренду

        foreach($parts as $part)
        {
            foreach($part->categories as $category)
            {
                // Привязываем категорию к бренду через pivot;
            }
        }

Another question is when, for example, I need a tricky catalog or something non-standard, as I threw off above + a large number of records, but I can, for example, make a pivot_brand_categories pivot table and in the background, I use php workers and a queue manager, once a day, for example, sort through all categories and link them to the brand, and on request, simply give the necessary categories, without long calls to the database. How good is this solution?
In general, while I settled on this option, the required functions are performed by:

public function partsByBrand($brand, $category)
    {
        if(isset($category) && !empty($category))
        {
            $parts = Part::where('brand_id', '=', $brand->id)->whereHas('categories', function ($query) use ($category)
            {
                return $query->where('category_id', $category->id);
            })->get();

            return $parts;
        }

        $brand_categories = PartCategory::whereHas('parts', function($query) use ($brand)
        {
            return $query->whereHas('brand', function($query) use ($brand) {
                return $query->where('brand_id', $brand->id);
            });
        })->orderBy('created_at', 'desc')->get();

        return $brand_categories;
    }