Handling tree in a MySQL procedure

De openkb
Aller à : Navigation, rechercher

Sommaire

Questions

The idea is simple - I have two tables, categories and products.

Categories:

id | parent_id | name               | count
1    NULL        Literature           6020
2    1           Interesting books    1000
3    1           Horrible books       5000
4    1           Books to burn        20
5    NULL        Motorized vehicles   1000
6    5           Cars                 999
7    5           Motorbikes           1
...

Products:

id | category_id | name
1    1             Cooking for dummies
2    3             Twilight saga
3    5             My grandpa s car
...

Now while displayed, the parent category contains all the products of all the children categories. Any category may have children categories . The count field in the table structure contains (or at least I want it to contain) count of all products displayed in this particular category. On the front-end, I select all subcategories with a simple recursive function, however I m not so sure how to do this in a SQL procedure (yes it has to be a SQL procedure ).The tables contain about a hundread categories of any kind and there are over 100 000 products.
Any ideas?

Answers

http://www.slideshare.net/billkarwin/models-for-hierarchical-data http://www.slideshare.net/billkarwin/models-for-hierarchical-data

For my Adjacency tables, I solve it by storing / caching the path (possibly in a script, or in a before update trigger ), on change of parent_id id, a new path-string is created. Your current table would look like this:

id | parent_id | path    | name               | count
1    NULL        1         Literature           6020
2    1           1:2       Interesting books    1000
3    1           1:3       Horrible books       5000
4    1           1:4       Books to burn        20
5    NULL        5         Motorized vehicles   1000
6    5           5:6       Cars                 999
7    5           5:7       Motorbikes           1

(choose any delimiter not found in the id you like)

So, now to get all products from a category + subcategories:

SELECT p.*
FROM categories c_main
JOIN categories c_subs
ON c_subs.id = c_main.id 
   OR c_subs.path LIKE CONCAT(c_main, :% )
JOIN products p
ON p.category_id = c_subs.id
WHERE c_main.id = <id>

Source

License : cc by-sa 3.0

http://stackoverflow.com/questions/3812621/handling-tree-in-a-mysql-procedure

Related

Outils personnels
Espaces de noms

Variantes
Actions
Navigation
Outils