Need help with a sql-statement related to nested sets
Hello girls and guys,
I'm trying since three days to get the first-level subcategories of one
categoriy without their childs. :(
I hope this can be understood, because my english isn't really very well...
I can't find any example or solution about this in the net. And all my
tries to rebuild the statement which selects all categories failed...
I'm really hoping that someone knows a solution for this... :(
My knowledge about nested sets are not very good and the statements have to
run on postgresql as well as with mysql. :(
Well here are the information you'll need to try this:
This is the table-structure of the categories table:
------------------------------ snip ------------------------------------
$q = "CREATE TABLE {$serendipity['dbPrefix']}dma_downloadmanager_categories
(
node_id {AUTOINCREMENT} {PRIMARY},
root_id int(10) NOT NULL default '0',
payload varchar(64),
lft int(10) NOT NULL default '0',
rgt int(10) NOT NULL default '0'
)";
------------------------------ snap ------------------------------------
where "payload" is the name of the category, the rest should be known as a
normal nested set table.
These "variables" in the CREATE statement are serendipity related and correct.
This is the statement which takes *all* categories with there levels and
subcategories, etc.:
------------------------------ snip ------------------------------------
$sql = "SELECT node1.node_id AS node_id,
node1.root_id AS root_id,
node1.payload AS payload,
node1.lft AS lft,
node1.rgt AS rgt,
round((node1.rgt-node1.lft-1)/2,0) AS subcats,
((min(node2.rgt)-node1.rgt-(node1.lft>1))/2) > 0 AS lower,
(( (node1.lft-max(node2.lft)>1) )) AS upper,
COUNT(*) AS level
FROM {$serendipity['dbPrefix']}dma_downloadmanager_categories AS node1,
{$serendipity['dbPrefix']}dma_downloadmanager_categories 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, node1.rgt, node1.node_id, node1.root_id,
node1.payload";
------------------------------ snap ------------------------------------
see, if a category has subcategories and you'll click on this category I
want to show these subcategories, but without their childs. Only the
first-level subcategories without their childs should be shown.
Oh... one more thing:
I've only one single ROOT-category, so all root_id's are "1".
I hope someone can help me here...
Sincerely
Alex (dma147)
Edited 1 time(s). Last edit at 09/17/2005 01:32AM by Alexander Mieland.