Query against LARAVEL database?

Question

Laravel

Hello everyone, dear programmers) I would be grateful for your help.

It is necessary to correctly compose a query to the database.

1. table shops (shop)
id,

2. table shop_map_point (point on the map, each store can have several points)
id, city_id

3. table products (product)
id, shop_id, category_id

4. table categories (category)
id,

5. city (cities)
id,

We are in the category id = 5, in the city id = 10 You need to select all the products that

- THE SHOP has a POINT ON THE MAP (city_id = 10)
- These STORES have an ITEM in this CATEGORY (category_id = 5)

use len.load to the product (so that when you foriech you can add the name of the store to the product without unnecessary requests) ->with()

Thank you in advance.
Do I need to use joins or can I do without them?

Answer 1

whereHas

Product::where('category_id', 5)
    ->whereHas('shops', function ($query) {
        $query ->whereHas('mapPoints', function ($query) {
           $query->where('city_id', 10);
        });
})
->with(...)
->get();

with similarly, the request can be placed in scope, see the documentation.