MySQL Forums
Forum List  »  Newbie

Re: Query to display categories and subcategories
Posted by: Chris Stubben
Date: May 06, 2005 04:22PM

Hi,

Check this recent article on trees in mysql.

http://www.vbmysql.com/articles/database-design/managing-hierarchical-data-in-mysql.html


If you know the height of your tree (in your example there are three levels), you can use self joins to display paths using one join for each level in the tree. Note this displays one column for each level in the tree.

Something like this.


SELECT t1.name as t1, t2.name as t2, t3.name as t3
FROM cat t1
LEFT JOIN cat t2 ON t1.id_category=t2.id_parent
LEFT JOIN cat t3 ON t2.id_category=t3.id_parent
WHERE t1.id_parent=0;



If you want all subcategories, subsubcategories, etc listed in a single column, you should use recursion (via a dbi script), nested set models, or you can convert your tree to a path enumerated list below.


I will use the table from the web site link, but now I'm searching up the tree instead of down.


SELECT t4.name as t4, t3.name as t3, t2.name as t2, t1.name as t1
FROM category t1
LEFT JOIN category t2 ON t1.parent=t2.category_id
LEFT JOIN category t3 ON t2.parent=t3.category_id
LEFT JOIN category t4 ON t3.parent=t4.category_id

+-------------+----------------------+----------------------+----------------------+
| t4 | t3 | t2 | t1 |
+-------------+----------------------+----------------------+----------------------+
| NULL | NULL | NULL | ELECTRONICS |
| NULL | NULL | ELECTRONICS | TELEVISIONS |
| NULL | ELECTRONICS | TELEVISIONS | TUBE |
| NULL | ELECTRONICS | TELEVISIONS | LCD |
| NULL | ELECTRONICS | TELEVISIONS | PLASMA |
| NULL | NULL | ELECTRONICS | PORTABLE ELECTRONICS |
| NULL | ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| NULL | ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS |
| NULL | ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS |
+-------------+----------------------+----------------------+----------------------+

Basically, just count up the number of non-null columns to get the level for each name in the last column, and concatenate non-null columns to get the path.



create table category_path as
SELECT t1.category_id, t1.name,
( if(t4.category_id is null, 0,1) + if(t3.category_id is null, 0,1) + if(t2.category_id is null, 0,1) + if(t1.category_id is null, 0,1)) as level,
trim(leading '; ' from concat(ifnull(t4.name, ''), '; ', ifnull(t3.name, ''), '; ', ifnull(t2.name, ''), '; ', ifnull(t1.name, ''))) as path
FROM category t1
LEFT JOIN category t2 ON t1.parent=t2.category_id
LEFT JOIN category t3 ON t2.parent=t3.category_id
LEFT JOIN category t4 ON t3.parent=t4.category_id;


+-------------+----------------------+-------+-------------------------------------------------------+
| category_id | name | level | path |
+-------------+----------------------+-------+-------------------------------------------------------+
| 1 | ELECTRONICS | 1 | ELECTRONICS |
| 2 | TELEVISIONS | 2 | ELECTRONICS; TELEVISIONS |
| 3 | TUBE | 3 | ELECTRONICS; TELEVISIONS; TUBE |
| 4 | LCD | 3 | ELECTRONICS; TELEVISIONS; LCD |
| 5 | PLASMA | 3 | ELECTRONICS; TELEVISIONS; PLASMA |
| 6 | PORTABLE ELECTRONICS | 2 | ELECTRONICS; PORTABLE ELECTRONICS |
| 7 | MP3 PLAYERS | 3 | ELECTRONICS; PORTABLE ELECTRONICS; MP3 PLAYERS |
| 8 | FLASH | 4 | ELECTRONICS; PORTABLE ELECTRONICS; MP3 PLAYERS; FLASH |
| 9 | CD PLAYERS | 3 | ELECTRONICS; PORTABLE ELECTRONICS; CD PLAYERS |
| 10 | 2 WAY RADIOS | 3 | ELECTRONICS; PORTABLE ELECTRONICS; 2 WAY RADIOS |
+-------------+----------------------+-------+-------------------------------------------------------+


Now you can print an indented tree

select concat(space(3*(level-1)), name) as tree from category_path order by path;

or display all subcategories under any node by searching the path string using LIKE (not very fast for large tables though).

select name from category_path where path like '%TELEVISIONS;%';


Chris

Options: ReplyQuote


Subject
Written By
Posted
Re: Query to display categories and subcategories
May 06, 2005 04:22PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.