Nested Set - erweiterte Baumstruktur

[tomdiddle]

hallo mysql-sql-denker

es gibt eine tabelle productgroups ( s.u.) mit der eine produktgruppen-baumstruktur nach dem nested-set modell abgebildet wird
( die trivialere rekursive vater-sohn methode funktioniert zwar auch, jedoch für die zu erwartende grösse und tiefe einfach nicht mehr performant genug )

ausserdem gibt es eine tabelle products, mit der produkte den entsprechenden gruppen zugeordnet werden ( productgroups.node_id = products.productgroupsid )

das pflegen und administrieren dieser struktur stellt keine probleme dar, jedoch zerbreche ich mir den kopf über ein performantes select-statement:

ich würde gerne zu jeder produktgruppe ( node_id o. root_id ) gleichzeitig auch die gesamt-anzahl der produkte all diesen gruppen untergordneten gruppen anzeigen lassen ( dabei sollte klar sein, dass ich nur produkte denjenigen gruppen zuordnen kann, die keine untergruppen haben )

also nach u.a. beispieldaten:
gruppen: anzahl produkte in allen untergruppen:
-A      2
 -A1    2
-C     1
 -C1    1
   -C1I  1
    -C1II 1
-E     0


das select zum anzeigen des baumes an sich birgt keine grosse geheimnisse:

SELECT node1.payload, node1.node_id,
COUNT(*) AS level FROM productgroups AS node1,
productgroups AS node2 WHERE node1.root_id > 1
AND node2.root_id > 1 AND node1.lft
BETWEEN node2.lft AND node2.rgt
GROUP BY node1.LFT

jedoch frage ich mich, wie ich dort in mysql-sql in einem select auch die counts auf die produkte hinbekomme ?!
vielleicht kann mir von euch dabei jemand auf die sprünge helfen ?

grüsse
tom





tabellen + daten:
CREATE TABLE productgroups (
node_id int(10) unsigned NOT NULL auto_increment,
root_id int(10) unsigned NOT NULL default '0',
name varchar(64) default NULL,
lft int(10) unsigned NOT NULL default '0',
rgt int(10) unsigned NOT NULL default '0',
activ set('Y','N') NOT NULL default 'Y'
PRIMARY KEY (node_id),
KEY root_id (root_id)
) TYPE=MyISAM;

INSERT INTO productgroups VALUES (1, 1, 'Wurzel', 1, 16);
INSERT INTO productgroups VALUES (2, 2, 'A', 2, 5);
INSERT INTO productgroups VALUES (4, 4, 'C', 6, 13);
INSERT INTO productgroups VALUES (5, 5, 'A1', 3, 4);
INSERT INTO productgroups VALUES (8, 8, 'C1', 7, 12);
INSERT INTO productgroups VALUES (10, 10, 'C1I', 8, 9);
INSERT INTO productgroups VALUES (11, 11, 'C1II', 10, 11);
INSERT INTO productgroups VALUES (13, 13, 'E', 14, 15);


CREATE TABLE products (
id int(11) unsigned NOT NULL auto_increment,
productgroupsid int(11) unsigned NOT NULL default '0',
name varchar(100) NOT NULL default '',
activ set('Y','N') NOT NULL default 'Y',
PRIMARY KEY (id),
KEY nestedid (nestedid)
) TYPE=MyISAM;

INSERT INTO products VALUES (1, 5, 'produkt1', 'Y');
INSERT INTO products VALUES (2, 5, 'produkt2', 'Y');
INSERT INTO products VALUES (3, 11, 'produkt3', 'Y');