mr-smor/DaringCart — Issue #5: MPTT for categories
Author: nuth
Created: 2011-10-13 17:17:49+00:00
State: open

Hi, I suggest we look in to using a MPTT in the db for the categories. I know MPTT can be slow to update, though I imagine that only becomes a problem if you have like 10,000 categories, but they're fast and flexible to use otherwise.

We could then for example get the tree structure and the count of subcategories of each category in one query, like so:

select group_concat(distinct m2.title separator ' > ') as parents,
m1.title, count(distinct m3.nodeid) as count
from mptt m1 left join mptt m2 on (m2.lft < m1.lft and m2.rgt > m1.rgt)
left join mptt m3 on (m3.lft > m1.lft and m3.rgt < m1.rgt)
group by m1.nodeid order by m1.lft;

That query would produce this result:

+---------------------+----------+-------+
| parents | title | count |
+---------------------+----------+-------+
| NULL | umu | 7 |
| umu | test | 4 |
| umu > test | test 3 | 2 |
| umu > test > test 3 | test 5 | 0 |
| umu > test > test 3 | test 6 | 0 |
| umu > test | test 4 | 0 |
| umu | test 2 | 0 |
| umu | test 3 | 0 |
| NULL | new root | 0 |
+---------------------+----------+-------+