What's the nicest and most convenient way to create a nested hierarchical structure in MySQL, well, the simplest example is a basic declaration directory. There are categories, they can be one in another (subcategories).
I am using a table with categories, which has a category id and a parent category id (0 for root). Is it pathetic? If yes, then tell me how to do it normally, otherwise tell me how, with such a system, you can humanly count the number of records from another table, "belonging" to a category (that is, not only purely to her, it is elementary, but everything nested).

  • Answer # 1

    MySQL is a relational DBMS, it is impossible to store hierarchical data in a "nice" form, so your option is not only acceptable, but also quite correct. Queries to such a structure in its pure form are impossible in MySQL, but you can write a stored procedure that recursively pulls out the id-shniki of ads of the desired category and all its subcategories. Herethere is an article on this topic. Having pulled out the id-shniki, it will not be difficult to count them.

    Thank you very much, now I will read the article. For, as I understand it, it is simply impossible to do this through MySQL (without procedures, as I did before) or through PHP, if the nesting level is greater than at least 3.

    Олег Архипов2021-02-23 02:21:38

    Hmm, in the same article it is said that in MySQL this cannot be done by means of a DBMS, only externally (like PHP), but everything about Oracle is painted.

    Олег Архипов2021-02-23 02:21:38

    Sorry, wrong link. [Here] [1] on MySQL. [1] club.shelek.ru/viewart.php?id=307

    fori1ton2021-02-23 02:21:38

    @ fori1ton, I have already googled this article myself, I read it, but thanks anyway, through the first article I realized that this should be looked for as "hierarchical queries", and before that I could not google it myself.

    Олег Архипов2021-02-23 02:21:38