Hi an alle,
hoffe, ihr könnt mir weiter helfen!!
Habe meine Kategorie als nested set gemacht
und gebe die Kategorien nur immer tiefenweise aus.
Nun möchte ich aber, daß zu den Kategorien auch die
Anzahl der Artikel steht, bekomme es aber irgendwie nicht hin.
Meine Abfrage lautet so :
PHP-Code:
$query_test = "SELECT node.node_id, node.payload, (
COUNT( parent.payload ) - ( sub_tree.depth +1 )
) AS depth, COUNT( product.name )
FROM node AS node,
node AS parent,
node AS sub_parent,
node AS count, product,
(
SELECT node.payload, (
COUNT( parent.payload ) -1) AS depth
FROM node AS node,
node AS parent
WHERE node.lft
BETWEEN parent.lft
AND parent.rgt
AND node.node_id =1
GROUP BY node.payload
ORDER BY node.lft
)
AS sub_tree
WHERE node.lft
BETWEEN parent.lft
AND parent.rgt
AND node.lft
BETWEEN sub_parent.lft
AND sub_parent.rgt
AND sub_parent.payload = sub_tree.payload
AND node.node_id = product.node_id
GROUP BY node.payload
HAVING depth =1
ORDER BY node.lft";
Er bringt mir zwar keine Fehlermeldung, aber auch kein
resultat, obwohl er eins bringen müßte.
Habe zwei Scripte, die ich zusammenfassen wollte,
das erste List mir die Kategorien Levelweise aus :
PHP-Code:
$query_navivert = "SELECT node.node_id,node.payload, (COUNT(parent.payload) - (sub_tree.depth + 1)) AS depth
FROM node AS node,
node AS parent,
node AS sub_parent,
(
SELECT node.payload, (COUNT(parent.payload) - 1) AS depth
FROM node AS node,
node AS parent
WHERE node.lft
BETWEEN parent.lft AND parent.rgt
AND node.node_id= '$id'
GROUP BY node.payload
ORDER BY node.lft
)
AS sub_tree WHERE node.lft
BETWEEN parent.lft AND parent.rgt
AND node.lft
BETWEEN sub_parent.lft
AND sub_parent.rgt
AND sub_parent.payload = sub_tree.payload
GROUP BY node.payload
HAVING depth = 1
ORDER BY node.lft";
Das zweite zählt die Artikel in den Kategorien:
PHP-Code:
$query_list = "
SELECT parent.payload, COUNT(product.name)
FROM node AS node ,
node AS parent, product
WHERE node.lft BETWEEN parent.lft AND parent.rgt
AND node.node_id = product.node_id
GROUP BY parent.payload
ORDER BY node.lft; ";
Hoffe, mir kann jemand weiter helfen.
Gruß
Chris