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