groupings

[toenailsin]

i'm trying to think of a way to have a whole tree of groupings and have a query to grab the entire path to the root group

eg, if i query group id 3, it will grab 1, 2 & 3.

CREATE TABLE IF NOT EXISTS `groups` (
`id` int(10) unsigned NOT NULL auto_increment,
`parentId` int(10) unsigned NOT NULL DEFAULT '0',
`name` varchar(32) NOT NULL,
PRIMARY KEY (`id`),
KEY `parentId` (`parentId`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

INSERT INTO `groups` (`id`, `parentId`, `name`) VALUES
(1, 0, 'root group'),
(2, 1, 'sub group'),
(3, 2, 'sub group 2'),
(4, 0, 'test'),
(5, 4, 'blah');

[toenailsin]

the best method i can think of is to have a field with all the id's of the parent groupings that get updated when you create/edit the group, and then have a query such as the following

however, i'm pretty sure this method wont be very good for indexing.


# select the wanted group
SELECT *
FROM groups
WHERE id = 3
# union select all the parent groups
UNION SELECT *
FROM groups
WHERE id IN (
# returns '2,1'
SELECT parentIds
FROM groups
WHERE id = 3
)

[ssystems]

try this "concept"


SELECT columnlist FROM table t1 join (select columnlist from t1) AS T2 ON t1.id=t2.id