Counting the number of items in categories and subcategories (SQL)?

Question

PHP

There was a problem with counting the number of items in categories and subcategories. Each category has subcategories, and subcategories have their own subcategories. The number of such levels is unlimited. Records are stored in the "bottom" category. Categories and records are represented as separate tables in the database.
The table structure is something like this:

Categories
-------------
id
parent_id
....
Posts
-------------
id
....
category_id
.....

You need to count the number of items in each category including subcategories. All this can be represented in the form of a tree, and theoretically it is possible to recursively count the number of elements at the top of the tree. But the problem is how to do it in the form of SQL queries to the database, so as not to heavily load it. Is it possible to somehow change the table structure?
For a separate category, I did something like this:

SELECT categories.*,(SELECT COUNT(posts.id) FROM posts WHERE posts.category_id = categories.id) AS count FROM categories WHERE 1

Thank you.

Answer 1

can ROLLUP help you ?

Answer 2

Hello.
Yes, sir. What if it's like this? The request has not been tested. Just like an idea. On a whim...

SELECT par.Id, COUNT(po.id) AS postCount
FROM Categories AS par
INNER JOIN Categories AS sub ON par.id = sub.parent_id
INNER JOIN Posts AS po ON po.id = sub.id
GROUP BY par.Id

Answer 3

Dig towards nested sets for the category table. Then everything will be much easier.