Laravel count news in selected category (including subcategories)?

Question

MySQL

Hello, I can’t figure out how to get the number of ads in the selected category (including subcategories) through ORM:
There are categories and subcategories, for example:
Auto
-bwm
-audi
Moto
-irbis
-polaris there is a posts
table with ads , there is also a posts_categories table type: post_id | categories_id and a table with categories categories id | name | parent




Can you please tell me how to get the number of ads in the selected category (including subcategories) through ORM? for example get the number of ads in the category Auto (count the number of ads from subcategories bwm and audi)
I only have an idea to first get a list of all subcategories like so:

$categories = Categories::where('parent', '=', $id)->get();

And then, through the loop, count the number of news items in each category, but that's not entirely correct, is it?
i.e. if this is done simply on MYSQL queries, then there should be something like:
SELECT * FROM categories WHERE parent = '1'; // get a list of child categories (for example, got id 2,3,4)
then
count how many posts the child elements have, for example:
SELECT COUNT(*) FROM post_categories WHERE categories_id = 2;
SELECT COUNT(*) FROM post_categories WHERE categories_id = 3;
SELECT COUNT(*) FROM post_categories WHERE categories_id = 4;
and you need to add the result of these 3 queries

Answer 1

In haste, if I understand correctly, the output to the site:

@foreach(Categories::all() as $category)
{{ $category->name }} <span class="badge">{{ Post_categories::where('categories_id', $category->id)->count() }}</span>
@endif

How are the models built?
UPD
Model for categories:

class Categories extends Model {

    public function posts()
    {
//Получаем множество постов, опубликованных в категории
        return $this->hasMany('App\PostCategories', 'categories_id', 'id');
    }

}

In the controller:

public function index(){
//Получаем коллекцию категорий, где есть посты. Есть постов в категории нет, то ее не будет в выборке.
        $categories = Categories::has('posts')->get();
        return view('index', compact('categories'));
    }

In the view:

@foreach($categories as $category)
{{ $category->name }} <span class="badge">{{ $category->posts->count() }}</span>
@endif

Answer 2

It seems to me that it is possible to make Categories hasOne() on the parent category and count it as an option via eager loading. Or I misunderstood the question

Answer 3

It looks like you just don't know that in addition to the '=' operator, there is also an ' IN ' operator in SQL. This is to combine 3 requests into one :)