How to properly build a query (Laravel)?

Question

ORM

Good day everyone! And good mood!
There was a question on the correct construction of request
There is a table with regions:
Table name `regions`
`region_id` (int);
`region_name`(varchar);
`region_code`(int);
Table with categories:
Table name `categories`
`cat_id` (int);
`cat_name`(varchar);
Table with goods:
Table name `goods`
`good_id` (int);
`good_name`(varchar);
`good_region`(int);
`good_cat`(int);
I want to make the link look like this site.ru/moskow/audio/2452145
in route.php I write a route like this:

Route::get('/{region}/{category}/{id}','[email protected]');

The contents of the GoodsController show method

public function show($region, $category, $id){
//тут у меня тупик
}

Now the question is:
1. It is necessary to check if there is a region in the database with the name Moscow, and pull out its ID
2. It is necessary to check if there is such a category in the database and pull out its ID
3. Check if the product belongs to this category and to this region
4. If everything is correct, then show the product
5. If it does not belong to the region or does not belong to the category, then 404
How can I correctly build such a query and / or several queries so as not to fence the garden ))
Thank you!

Answer 1

It is assumed that the links are configured correctly.
And don't forget the indexes on the fields cat_name, region_name.
Link to documentation .

$result = Goods::query()
    ->whereHas('category', function ($query) use ($category) {
        /** @var \Illuminate\Database\Query\Builder $query */
        $query->where('cat_name', $category);
    })
    ->whereHas('region', function ($query) use ($region) {
        /** @var \Illuminate\Database\Query\Builder $query */
        $query->where('region_name', $region);  
    })
    ->where('id', $id)
    ->with('category', 'region')
    ->first(); // можно ->firstOrFail() = само выкинет ошибку

if ($result === null) {
    // 404
}

Answer 2

First, make connections in the models. So it will be easier.

public function show($region, $category, $id){
$Region = Region::where('region_code', '=', $region)->first();
$Category = Category::where('cat_name', '=', $category)->first();
$Region->products()->where('good_region', $Region->id)->where('good_cat', '=', $Category->id)->where('id', '=', $id)->first();
return view(...) 
}

This is the most primitive option. You can do everything in one line. But you will learn eloquent and you will understand how to do it
yourself. Arrange the checks yourself.

Answer 3

You can bind models in routes.

Route::model('region', \App\Models\Region::class);
Route::model('category', \App\Models\Category::class);
Route::model('good', \App\Models\Good::class);

Route::get('/{region}/{category}/{good}', '[email protected]');

Models are injected into routes and automatically checked for existence. The controller will have ready-made model instances.

public function show(Region $region, Category $category, Good $good){
//
}

You will only have to check if the product belongs to this category and to this region.