Output of articles, tree

mysql database,
there is a table

**page**
     - id
     - name
     - cat

and

**cat**
 - id
 - title
 - parent_id (NULL)

let’s say a category is selected, you need to display the last (id DESC) pages (page)
with a limit (LIMIT) from the subcategories of this category (well, respectively, from this cat.)


Answer 1

The query will select all posts that match category 123 and its subcategories by 1 nesting level.

SELECT *
FROM page p
LEFT JOIN cat  ON c.id = p.cat
WHERE
    (p.cat = 123) OR
    (c.parent_id = 123)
ORDER BY p.id DESC
LIMIT 10

Two levels of nesting:

SELECT *
FROM page p
LEFT JOIN cat 1 ON c.id = p.cat
LEFT JOIN cat 2 ON c2.id = c1.parent_id
WHERE
    (p.cat = 123) OR
    (c1.parent_id = 123) OR
    (c2.parent_id = 123)
ORDER BY p.id DESC
LIMIT 10

Unfortunately, this type of tree does not allow you to select the entire nested tree depth-wise without adding a LEFT JOIN at each next level.