MySQL Forums
Forum List  »  PHP

Need help with a sql-statement related to nested sets
Posted by: Alexander Mieland
Date: September 17, 2005 01:30AM

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.

Options: ReplyQuote


Subject
Written By
Posted
Need help with a sql-statement related to nested sets
September 17, 2005 01:30AM


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.