SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Categories/Sub Categories

    Hello,

    I am trying to add the ability to use sub categories to my database and I need help with a query. My design:

    Code:
    CREATE TABLE `Articles` (
      `ID` int(11) unsigned NOT NULL auto_increment,
      `Name` text NOT NULL,
      `Body` longtext NOT NULL,
      `Date` datetime NOT NULL default '0000-00-00 00:00:00',
      `Picture` varchar(100) default NULL,
      `Description` text NOT NULL
      PRIMARY KEY  (`ID`),
    )
    
    CREATE TABLE `Categories` (
      `ID` int(11) unsigned NOT NULL auto_increment,
      `ParentID` int(11) unsigned NOT NULL default '0',
      `Name` varchar(100) NOT NULL default ''
      PRIMARY KEY  (`ID`),
      UNIQUE KEY `Name` (`Name`)
    )
    
    CREATE TABLE `CatLookup` (
      `ArticleID` int(11) unsigned NOT NULL default '0',
      `CatID` int(11) unsigned NOT NULL default '0',
      PRIMARY KEY  (`ArticleID`,`CatID`),
      KEY `CatID` (`CatID`)
    )
    What I would like to do, is pull out all the articles that are for a particular category, and ALL articles in ALL sub categories for that category. I am having trouble with the SQL. I know how to get the articles from one particular category (a la WHERE CatID = 3 ...), but I am having trouble translating that into many categories. I think I need to somehow get the ID's of the children, then pass them into the WHERE clause (WHERE CatID = 3, 4, 5, 6, etc...), but I'm not familiar with how this would be done. Any ideas?

    Ben

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    this is going to be tricky if you have more than fourteen levels of sub-sub-sub-...sub-categories

    how many levels do you have, maximum?
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well perhaps about 4 maximum right now. I haven't actually added any yet as I want to get a page up that can list the articles before I add them. If its tricky am I doing this wrong then? I see lots of sites that have sub categories (I.e SitePoint) are they using a different method?

    Ben

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    no, sorry, i did not mean to imply that you're doing anything wrong

    four levels is fine, and quite manageable

    have a look at this query (whoa, i can't believe it's been 4 years!)

    that's the basic query structure, and what a coincidence, you used exactly the same column names in your categories table, so that query should run as is for you

    let me know what you think so far, and then we'll get to the tricky bit

    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    74
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    nested set

    have you heared about "nested set" used to present tree like structure? searching google to find what is it.

    it is a almost perfect solution for catgegory and sub-category problem.
    but you need do some programming job to make it work.

    regards.

  6. #6
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is an article I used to create a class to manage my category tree. I'll see if I can clean the class file and get all of our proprietary stuff out and post it here.

    http://dev.mysql.com/tech-resources/...ical-data.html

    It explains the two models Adjacency List model and the Nested Set model. For large complex trees you want to use the nested set model.

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2007
    Posts
    40
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    this is going to be tricky if you have more than fourteen levels of sub-sub-sub-...sub-categories
    Does it takes too much time or LEFT JOIN does not support it?

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    tricky to write the sql, and tricky to write the code which handles the result set

    otherwise, it runs just fine (see this thread)
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Wizard
    Join Date
    Jan 2001
    Location
    Grand Rapids, MI
    Posts
    1,284
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    no, sorry, i did not mean to imply that you're doing anything wrong

    four levels is fine, and quite manageable

    have a look at this query (whoa, i can't believe it's been 4 years!)

    that's the basic query structure, and what a coincidence, you used exactly the same column names in your categories table, so that query should run as is for you

    let me know what you think so far, and then we'll get to the tricky bit

    Ok I ran that query and I figured out that much so far. I think then its probably best to limit the categories to 4 deep maximum. So from what I understand then, other sites that use this model either 1. limit the depth, or 2. only show the articles in the direct child category only? I think i'm starting to understand. What I need help doing now is actually pulling the articles out.

    Thanks!

  10. #10
    SitePoint Member
    Join Date
    Jul 2007
    Posts
    13
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Here is the class file I mentioned earlier. It uses the Nested Set method and allows speedy loading of a tree. I had to do a lot of renaming and had to remove a little internal code that I couldn't share so there may be a tiny bug or two because of that.

    I've included the Mysql 5 table definition so you're not left guessing how to recreate it and I've included some examples of use in the comments of the class.

    Everyone let me know if you find this at all helpful or have improvements to share.

    PHP Code:
    <?php
    /**
     * Category_Tree PHP4 script by Michael Stack
     *
     * This is a no-frills class that gets the job done. No multi category
     * parenting or anything that links to products or whatever you sell or list.
     */

    /*

    Database Table Creation Statement

    CREATE TABLE `CATEGORIES` (
     `CATEGORIES_ID` int(10) unsigned NOT NULL auto_increment,
     `FOREST_ID` int(10) unsigned NOT NULL default '0',
     `CATEGORY` char(32) NOT NULL default '',
     `URLNAME` char(32) default NULL,
     `PATH` text,
     `LFT` int(10) NOT NULL default '0',
     `RGT` int(10) NOT NULL default '0',
     PRIMARY KEY  (`CATEGORIES_ID`),
     KEY `FOREST_ID_IDX` (`FOREST_ID`)
    )

    */

    /*

    !!!!
    You will have to change the $this->dbh->execute lines to work with your DBH.
    It'll only take a little work to make it work with PDO though
    !!!!

    !!!!
    There is no input checking in this class. Do your input checking and escaping before sending it into here!
    !!!!

    Example of use:

    // Load an existing tree
    $catTree = new Category_Tree();
    $catTree->setDBH($DBH);
    $catTree->setForestID(4);
    $catTree->setTreeName('customer_1234_TREE');
    $tree = $catTree->loadTree();

    // Create a new tree
    $catTree = new Category_Tree();
    $catTree->setDBH($DBH);
    $catTree->setForestID(4);
    $catTree->setTreeName('customer_1235_TREE');
    $catTree->createTree();

    // Add a sibling node, addNodeBefore() is also available
    $catTree->addNodeAfter('Shirts', $this->findNodeByCategory('Pants'));

    // Add a child node as the first listed child
    $catTree->addNodeUnder('Shorts', $this->findNodeByCategory('Pants'));

    // Move a node, modeNodeBefore() and moveNodeUnder() are also available
    $catTree->modeNodeAfter($catTree->findNodeByCategory('Pants'), $catTree->findNodeByCategory('Shirts'));

    // Reload the tree
    $tree = $catTree->loadTree();

    There are many ways to find a node to pivot on.
    Category, ID, Left, Right, Path, Side, UrlName, Parent Of, Sibling After, Sibling Before

    A lot of sites like to place a bread crumbs  at the top of things like:
    > Shirts > Mens > Polos > [X Brand Name]
    You are given two bread crumb methods. Use either one depending on the direction you want to go.

    Very simple debug output. You will need to have executed loadTree() first as it works for the loaded structure not from teh database.
    $catTree->displayTree();
    */

    define('MAX_DEPTH'999);

    /**
     * Category_Tree
     *
     * A PHP4 class that uses the Nested Set method to store a tree structure in a database.
     *
     * If you find this class useful or make improvements drop me a line and let me know.
     * It is provided as-is with no guarantees or support.
     *
     * @author Michael Stack  <stack-cattree [at] landstander [fullstop] com>
     *
     *
     */
    class Category_Tree {

        
    /**
         * Tree's are grouped into forests. Think of forests as customers, store locations,
         * anything that might need different groups of trees.
         *
         * @var int
         */
        
    var $forestID 1;

        var 
    $tree NULL;

        
    /**
         * Defines the head/root node name for the tree.
         *
         * @var string
         */
        
    var $treeName 'DEFAULT_TREE';

        
    /**
         * The Tree Index is used when the tree has been loaded and has not changed since that time.
         * This will allow searching for a specific node without hitting the DB again.
         *
         * @var array
         */
        
    var $treeIndex NULL;

        
    /**
         * Safe URL characters
         *
         * @var ustring
         */
        
    var $urlRegExp '/[^0-9a-zA-Z\-]/';

        
    /*
         * Default to TRUE so that the object will still query the database if loadTree is never called.
         *
         */
        
    var $modified TRUE;

        
    /**
         * The DB handle
         *
         * @var DBH
         */
        
    var $dbh NULL;

        function 
    Category_Tree() {
        }

        function 
    setforestID($forestID) {
            
    $this->forestID $forestID;
        }

        function 
    setDBH(&$dbh) {
            
    $this->dbh $dbh;
        }

        
    /**
         * Sets the Tree's root node name
         *
         * @param string $treeName '*_TREE' string is expected.
         */
        
    function setTreeName($treeName) {
            if (!
    preg_match('/.*_tree$/i',$treeName)) {
                return 
    FALSE;
            }

            
    $this->treeName $treeName;

            return 
    TRUE;
        }

        
    /**
         * Creates a new tree in the forest.
         *
         * @return bool Success
         */
        
    function createTree() {
            
    // First check that this type of tree does not already exist for this forestID
            
    $sql "SELECT * FROM CATEGORIES WHERE CATEGORY = '?' AND FOREST_ID = ?";
            
    $result $this->dbh->execute($sql, array($this->treeName$this->forestID));

            if (
    $result) {
                return 
    FALSE;
            }

            if (!
    preg_match('/.*_tree$/i',$this->treeName)) {
                return 
    FALSE;
            }

            
    $urlName str_replace('-tree'''$this->_trimCategory($this->treeName));
            if (
    $this->findNodeByUrlName($urlName.'_tree') || $this->findNodeByCategory($this->treeName)) {
                return 
    FALSE;
            }

            
    $sql "SELECT MAX(RGT) AS RGT FROM CATEGORIES WHERE FOREST_ID = ? GROUP BY FOREST_ID ";
            
    $result $this->dbh->execute($sql, array($this->forestID));
            
    $left $result[0]['RGT']+1;

            
    $sql "INSERT INTO CATEGORIES (CATEGORY, URLNAME, PATH, FOREST_ID, LFT, RGT) VALUES ('?', '?', '?', ?, ?, ?)";
            
    $this->dbh->execute($sql, array($this->treeName$urlName.'_tree''/'.$urlName.'/'$this->forestID$left$left+1));

            return 
    TRUE;
        }

        
    /**
         * Gets the tree that forest_id and treeName point to
         *
         * @param string $sqlCache Pass in an empty string to rerun the query and not use the DB query cache results.
         * @return array Category Tree
         */
        
    function loadTree($sqlCache 'SQL_CACHE') {
            
    $this->treeIndex = array();

            
    $sql "SELECT {$sqlCache} node.*, (COUNT(parent.CATEGORY) - (sub_tree.depth + 1)) AS depth
                    FROM CATEGORIES AS node,
                        CATEGORIES AS parent,
                        CATEGORIES AS sub_parent,
                        (
                            SELECT node.CATEGORY, (COUNT(parent.CATEGORY) - 1) AS depth, node.FOREST_ID
                            FROM CATEGORIES AS node,
                            CATEGORIES AS parent
                            WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
                            AND node.CATEGORY = '?'
                            AND node.FOREST_ID = ?
                            AND parent.FOREST_ID = node.FOREST_ID
                            GROUP BY node.CATEGORY
                            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.CATEGORY = sub_tree.CATEGORY
                        AND parent.FOREST_ID = sub_parent.FOREST_ID
                        AND parent.FOREST_ID = sub_tree.FOREST_ID
                        AND node.FOREST_ID = ?
                        AND parent.FOREST_ID = ?
                    GROUP BY node.CATEGORY
                    ORDER BY node.LFT"
    ;

            
    $results $this->dbh->execute($sql, array($this->treeName$this->forestID$this->forestID$this->forestID));

            
    $parent array_shift($results);
            
    $tree $this->_dbNodeToNode($parent);
            
    $tree['parent_id'] = NULL;
            
    $tree['children'] = array();
            while(
    $data array_shift($results)) {
                
    $index count($tree['children']);
                
    $tree['children'][$index] = array();
                
    $tree['children'][$index] = $this->_addChild($data$tree['children'][$index], $results$tree['id']);
            }

            
    $this->treeIndex['category'][$parent['CATEGORY']] =& $tree;
            
    $this->treeIndex['id'][$parent['CATEGORIES_ID']] =& $tree;
            
    $this->treeIndex['urlName'][$parent['URLNAME']] =& $tree;
            
    $this->treeIndex['path'][$parent['PATH']] =& $tree;
            
    $this->treeIndex['LFT'][$parent['LFT']] =& $tree;
            
    $this->treeIndex['RGT'][$parent['RGT']] =& $tree;

            
    $this->tree $tree;

    /*
            If the categories have items or articles, or something then you can use a query like this to get the counts that belong to specific categories.

            $sql = "SELECT {$sqlCache} parent.CATEGORIES_ID AS id, COUNT(items.TITLE) AS itemsCount
                    FROM CATEGORIES AS node,
                        CATEGORIES AS parent,
                        {ITEM_TABLE} as items
                    WHERE node.LFT BETWEEN parent.LFT AND parent.RGT
                        AND node.CATEGORIES_ID = items.CATEGORIES_ID
                        AND node.FOREST_ID = ?
                        AND parent.FOREST_ID = ?
                    GROUP BY parent.CATEGORY
                    ORDER BY node.LFT";
            $results = $this->dbh->execute($sql, array($this->forestID, $this->forestID));

            if ($results) {
                foreach ($results as $result) {
                    if (isset($this->treeIndex['id'][$result['id']])) {
                        $this->treeIndex['id'][$result['id']]['items'] = $result['items'];
                    }
                }
            }
    */
            
    $this->modified FALSE;

            return 
    $this->tree;
        }

        
    /**
         * Finds a node by a CATEGORIES_ID
         *
         * @param int $id CATEGORIES_ID
         * @return mixed
         */
        
    function findNodeByID($id) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['id'][$id])) {
                return 
    $this->_returnNode($this->treeIndex['id'][$id]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds a node by either its LFT or RGT side.
         *
         * @param int $side A LFT or RGT value
         * @return mixed
         */
        
    function findNodeBySide($side) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['LFT'][$side])) {
                return 
    $this->_returnNode($this->treeIndex['LFT'][$side]);
            }
            if (isset(
    $this->treeIndex['RGT'][$side])) {
                return 
    $this->_returnNode($this->treeIndex['RGT'][$side]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds a node by its LFT side value
         *
         * @param int $left A LFT value
         * @return mixed
         */
        
    function findNodeByLeft($left) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['LFT'][$left])) {
                return 
    $this->_returnNode($this->treeIndex['LFT'][$left]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds a node by its RGT side value
         *
         * @param int $right A RGT value
         * @return mixed
         */
        
    function findNodeByRight($right) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['RGT'][$right])) {
                return 
    $this->_returnNode($this->treeIndex['RGT'][$right]);
            }
            return 
    FALSE;
        }

        
    /**
         * Find a node by the category name.
         *
         * @param string $category A category name
         * @return mixed
         */
        
    function findNodeByCategory($category) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['category'][$category])) {
                return 
    $this->_returnNode($this->treeIndex['category'][$category]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds a category by it's url as it would appear in a url.
         *
         * @param string $urlName A category url name
         * @return mixed
         */
        
    function findNodeByUrlName($urlName) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['urlName'][$urlName])) {
                return 
    $this->_returnNode($this->treeIndex['urlName'][$urlName]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds a category by it's full path.
         *
         * @param string $path A category path
         * @return mixed
         */
        
    function findNodeByPath($path) {
            
    $this->_checkTree();
            if (isset(
    $this->treeIndex['path'][$path])) {
                return 
    $this->_returnNode($this->treeIndex['path'][$path]);
            }
            return 
    FALSE;
        }

        
    /**
         * Finds and returns the sibling node that follows this node.
         *
         * @param array $node Category Node
         * @return mixed
         */
        
    function findSiblingAfter($node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            
    $this->_checkTree();

            if (
    $sibling $this->findNodeByLeft($node['RGT']+1)) {
                if (
    $sibling['parent_id'] == $node['parent_id']) {
                    return 
    $sibling;
                }
            }
            return 
    FALSE;
        }

        
    /**
         * Finds and returns the sibling node that preceeds this node.
         *
         * @param array $node Category Node
         * @return mixed
         */
        
    function findSiblingBefore($node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            
    $this->_checkTree();

            if (
    $sibling $this->findNodeByRight($node['LFT']-1)) {
                if ( 
    $sibling['parent_id'] == $node['parent_id']) {
                    return 
    $sibling;
                }
            }
            return 
    FALSE;
        }

        
    /**
         * Changes the category name and url name of the node. All children nodes will have their paths updated automatically.
         *
         * @param string $name New category name
         * @param array $node Category Node
         * @return mixed
         */
        
    function renameCategory($name$node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }

            if (
    $this->findNodeByCategory($name)) {
                return 
    FALSE;
            }

            
    $urlName $this->_trimCategory($name);

            if (
    $urlNode $this->findNodeByUrlName($urlName)) {
                if (
    $urlNode['id'] !== $node['id']) {
                    return 
    FALSE;
                }
            }
            
    $oldUrlName $node['urlName'];

            
    $sql "UPDATE CATEGORIES SET CATEGORY = '?', URLNAME = '?' WHERE CATEGORIES_ID = ? AND FOREST_ID = ? LIMIT 1";

            
    $this->dbh->execute($sql, array($name$urlName$node['id'], $this->forestID));

            
    $this->_updatePaths($oldUrlName$urlName$node['LFT'], $node['RGT']);
        }

        
    /**
         * Returns a list of nodes from the given node back to the root node.
         *
         * @param array $node Category Node
         * @param bool $omitRootNode True to omit the root node, which is often only used for grouping purposes.
         * @return mixed
         */
        
    function getBreadCrumbFromNode($node$omitRootNode TRUE) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_getBreadCrumb($node$omitRootNode'DESC');
        }

        
    /**
         * Returns a list of nodes from the root node to the given node.
         *
         * @param array $node Category Node
         * @param bool $omitRootNode True to omit the root node, which is often only used for grouping purposes.
         * @return mixed
         */
        
    function getBreadCrumbToNode($node$omitRootNode TRUE) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_getBreadCrumb($node$omitRootNode);
        }

        
    /**
         * Finds and returns the immediate parent of a node.
         *
         * @param array $node Category Node
         * @return mixed
         */
        
    function findParentOfNode($node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_getParentOfLftRgt($node['LFT'], $node['RGT']);
        }

        
    /**
         * Finds and returns the direct children of a node.
         *
         * @param array $node Category Node
         * @return mixed
         */
        
    function getChildrenOfNode($node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_subTree($node0);
        }

        
    /**
         * Finds and returns all the children under a node. The nodes returned are in a flat list in order of LFT.
         *
         * @param array $node Category Node
         * @param int $depth How many levels to drill down.
         * @return mixed
         */
        
    function getSubTreeUnderNode($node$depth MAX_DEPTH) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_subTree($node$depth);
        }

        
    /**
         * Adds a new sibling node before the given node.
         *
         * @param string $category A category name
         * @param array $node Category Node
         * @return bool Success
         */
        
    function addNodeBefore($category$node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_addNode($category$node['LFT']);
        }

        
    /**
         * Adds a new sibling node after the given node.
         *
         * @param string $category A category name
         * @param array $node Category Node
         * @return bool Success
         */
        
    function addNodeAfter($category$node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_addNode($category$node['RGT']+1);
        }

        
    /**
         * Adds a new child node under the given node.
         *
         * @param string $category A category name
         * @param array $node Category Node
         * @return bool Success
         */
        
    function addNodeUnder($category$node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_addNode($category$node['RGT']);
        }

        
    /**
         * Deletes the given node and ALL children located under it.
         *
         * @param array $node Category Node
         * @return bool Success
         */
        
    function deleteNode($node) {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }
            return 
    $this->_deleteRange($node['LFT'], $node['RGT']);
        }

        
    /**
         * Moves a node from it's current position to a new position in front of another node.
         *
         * @param array $fromNode Category Node (Node to be moved)
         * @param array $toNode Category Node (Node to preceed)
         * @return bool Success
         */
        
    function moveNodeBefore($fromNode$toNode) {
            if (!
    $this->_checkIsNode($fromNode) || !$this->_checkIsNode($toNode)) {
                return 
    FALSE;
            }

            return 
    $this->_moveNode($fromNode['LFT'], $toNode['LFT']);
        }

        
    /**
         * Moves a node from it's current position to a new position after another node.
         *
         * @param array $fromNode Category Node (Node to be moved)
         * @param array $toNode Category Node (Node to follow)
         * @return bool Success
         */
        
    function moveNodeAfter($fromNode$toNode) {
            if (!
    $this->_checkIsNode($fromNode) || !$this->_checkIsNode($toNode)) {
                return 
    FALSE;
            }

            return 
    $this->_moveNode($fromNode['LFT'], $toNode['RGT']+1);
        }

        
    /**
         * Moves a node it's current position to a new position as first child under another node.
         *
         * @param array $fromNode Category Node (Node to be moved)
         * @param array $toNode Category Node (Node to use as new parent)
         * @return bool Success
         */
        
    function moveNodeUnder($fromNode$toNode) {
            if (!
    $this->_checkIsNode($fromNode) || !$this->_checkIsNode($toNode)) {
                return 
    FALSE;
            }
            return 
    $this->_moveNode($fromNode['LFT'], $toNode['RGT']);
        }

        
    // Will insert a single node into the tree.
        
    function _addNode($category$right) {
            if (
    $this->findNodeByCategory($category)) {
                return 
    FALSE;
            }

            
    $urlName $this->_trimCategory($category);
            if (
    $this->findNodebyUrlName($urlName)) {
                return 
    FALSE;
            }

            
    $this->modified TRUE;

            
    $this->_shiftNode($right2);

            
    $sql "INSERT INTO CATEGORIES (CATEGORY, URLNAME, PATH, FOREST_ID , LFT, RGT) VALUES ('?', '?', '?', ?, ?, ?)";
            
    $this->dbh->execute($sql, array($category$urlName$urlName.'/'$this->forestID$right$right+1));

            
    $node $this->findNodeByLeft($right);

            if (!
    $node) {
                return 
    FALSE;
            }
            
    $parentNode $this->findParentOfNode($node);

            
    $this->_updatePaths($urlName.'/'$parentNode['path'].$node['urlName'].'/'$node['LFT'], $node['RGT']);

            return 
    $node['id'];

        }

        function 
    _getParentOfLftRgt($lft$rgt) {

            
    $this->_checkTree();

            return 
    $this->treeIndex['id'][$this->treeIndex['LFT'][$lft]['parent_id']];
        }

        function 
    _updatePaths($oldPath$newPath$lft$rgt) {
            
    $sql "UPDATE CATEGORIES SET PATH = REPLACE(PATH, '?', '?') WHERE LFT >= ? AND RGT <= ? AND FOREST_ID = ?";

            
    $result $this->dbh->execute($sql, array($oldPath$newPath$lft$rgt$this->forestID));

            return 
    TRUE;
        }

        function 
    _trimCategory($category) {
            
    $category strtolower(str_replace(array(' ''_'), '-'$category));

            return 
    preg_replace($this->urlRegExp''$category);
        }

        function 
    _deleteRange($left$right) {
            
    $this->modified TRUE;

            
    $range $right $left;

            
    $sql 'DELETE FROM CATEGORIES WHERE LFT >= ? AND RGT <= ? AND FOREST_ID = ?';
            
    $this->dbh->execute($sql, array($left$right$this->forestID));

            
    $sql 'UPDATE CATEGORIES SET LFT = LFT - ? WHERE LFT > ? AND FOREST_ID = ?';
            
    $this->dbh->execute($sql, array($range 1$right$this->forestID));

            
    $sql 'UPDATE CATEGORIES SET RGT = RGT - ? WHERE RGT > ? AND FOREST_ID = ?';
            
    $this->dbh->execute($sql, array($range 1$right -1$this->forestID));

            return 
    TRUE;
        }

        function 
    _shiftNode($left$shift) {
            
    $this->modified TRUE;

            
    $sql 'UPDATE CATEGORIES SET LFT = LFT + ? WHERE LFT >= ? AND FOREST_ID = ?';
            
    $this->dbh->execute($sql, array($shift$left$this->forestID));

            
    $sql 'UPDATE CATEGORIES SET RGT = RGT + ? WHERE RGT >= ? AND FOREST_ID = ?';
            
    $this->dbh->execute($sql, array($shift$left$this->forestID));

            return 
    TRUE;
        }

        function 
    _moveTree($left$right$shift) {
            
    $this->modified TRUE;

            
    $sql 'UPDATE CATEGORIES SET LFT = LFT + ?, RGT = RGT + ? WHERE LFT >= ? AND RGT <= ? AND FOREST_ID = ?';

            
    $this->dbh->execute($sql, array($shift$shift$left$right$this->forestID));

            return 
    TRUE;
        }

        function 
    _moveNode($fromLeft$toLeft) {
            
    $this->modified TRUE;

            
    $leftShift = ($fromLeft $toLeft) ? TRUE FALSE;

            
    $fromNode $this->findNodeByLeft($fromLeft);

            
    $oldPath $fromNode['path'];

            if (
    $fromNode['LFT'] < $toLeft && $fromNode['RGT'] > $toLeft) {
                return 
    FALSE;
            }

            
    $fromRight $fromNode['RGT'];

            
    $range $fromRight $fromLeft 1;

            
    $this->_shiftNode($toLeft$range);

            
    $left $fromLeft + (($leftShift) ? $range 0);
            
    $right $fromRight + (($leftShift) ? $range 0);
            
    $shift = (abs($fromLeft $toLeft) + (($leftShift) ? $range 0)) * ($leftShift ? -1);

            
    $this->_moveTree($left$right$shift);
            
    $this->_shiftNode($fromRight+1$range * -1);

            
    $newNode $this->findNodeByID($fromNode['id']);
            
    $parentNode $this->findParentOfNode($newNode);

            
    $this->_updatePaths($oldPath$parentNode['path'].$fromNode['urlName'].'/'$newNode['LFT'], $newNode['RGT']);

            return 
    TRUE;

        }

        function 
    _addChild($inData$tree, &$list$parent_id) {
            
    $tree $this->_dbNodeToNode($inData);
            
    $tree['parent_id'] = $parent_id;
            
    $tree['children'] = array();
            while(
    $data array_shift($list)) {
                if (
    $inData['RGT'] < $data['LFT']) {
                    
    array_unshift($list$data);
                    break;
                } else {
                    
    $index count($tree['children']);
                    
    $tree['children'][$index] = array();
                    
    $tree['children'][$index] = $this->_addChild($data$tree['children'][$index], $list$tree['id']);
                }
            }
            
    $this->treeIndex['category'][$inData['CATEGORY']] =& $tree;
            
    $this->treeIndex['id'][$inData['CATEGORIES_ID']] =& $tree;
            
    $this->treeIndex['urlName'][$inData['URLNAME']] =& $tree;
            
    $this->treeIndex['path'][$inData['PATH']] =& $tree;
            
    $this->treeIndex['LFT'][$inData['LFT']] =& $tree;
            
    $this->treeIndex['RGT'][$inData['RGT']] =& $tree;

            return 
    $tree;
        }

        function 
    _subTree($node$depth MAX_DEPTH) {

            
    $this->_checkTree();

            if (
    $depth <= 0) {
                return 
    array_slice($this->_returnNodeList($this->treeIndex['id'][$node['id']], 1), 1);
            } else {
                return 
    $this->_returnNodeList($this->treeIndex['id'][$node['id']], $depth);
            }
        }

        
    /**
         * Removes the children portion from the returned node.
         *
         * @param array $nodeData Category Node w/ children
         * @return array Category Node
         */
        
    function _returnNode($nodeData) {
            unset(
    $nodeData['children']);
            return 
    $nodeData;
        }

        function 
    _returnNodeList($nodeData$depth MAX_DEPTH$list NULL) {
            if (
    $list === NULL) {
                
    $list = array();
            }
            if (--
    $depth >= 0) {
                if(
    count($nodeData['children'])) {
                    
    $children array_reverse($nodeData['children']);
                    foreach(
    $children as $child) {
                        
    $list $this->_returnNodeList($child$depth$list);
                    }
                }
            }
            
    array_unshift($list$this->_returnNode($nodeData));
            return 
    $list;
        }

        function 
    _getBreadCrumb($node$omitRootNode TRUE$direction 'ASC') {
            if (!
    $this->_checkIsNode($node)) {
                return 
    FALSE;
            }

            
    $this->_checkTree();
            
    $list = array();

            do {
                
    $list[] = $node;
                
    $node $this->findNodeByID($node['parent_id']);
            } while (
    $node);
            if (
    $omitRootNode) {
                
    array_pop($list);
            }
            if (
    $direction == 'ASC') {
                
    $list array_reverse($list);
            }
            return 
    count($list) ? $list FALSE;
        }

        function 
    _dbNodeToNode($dbNode) {
            
    $node['category'] = $dbNode['CATEGORY'];
            
    $node['id'] = $dbNode['CATEGORIES_ID'];
            
    $node['urlName'] = $dbNode['URLNAME'];
            
    $node['path'] = $dbNode['PATH'];
            
    $node['LFT'] = $dbNode['LFT'];
            
    $node['RGT'] = $dbNode['RGT'];
            if (isset(
    $dbNode['depth'])) {
                
    $node['depth'] = $dbNode['depth'];
            } else {
                
    $node['depth'] = substr_count($dbNode['PATH'],'/') - 2;
            }
    /*        // Uncomment if you expect to use item counts.
            $node['items'] = 0;
    */
            
    if (isset($dbNode['parent_id'])) {
                
    $node['parent_id'] = $dbNode['parent_id'];
            } else {
                
    $node['parent_id'] = NULL;
            }
            return 
    $node;
        }

        function 
    _dumpTree($tree$level 0) {
            echo 
    str_repeat('..'$level);
            echo 
    "{$tree['id']}{$tree['category']} ({$tree['LFT']}{$tree['RGT']})\n";
            if (
    count($tree['children'])) {
                foreach (
    $tree['children'] as $childTree) {
                    
    $this->_dumpTree($childTree$level 1);
                }
            }
        }

        
    // For debugging purposes
        
    function displayTree() {
            
    $this->_dumpTree($this->tree);
        }

        function 
    _checkIsNode($node) {
            if (!isset(
    $node['LFT']) && !isset($node['RGT']) && !isset($node['id'])) {
                return 
    FALSE;
            }
            return 
    TRUE;
        }

        function 
    _checkTree() {
            if (
    $this->modified) {
                
    // Pass in a blank string so the data is not pulled from memcached.
                
    $this->loadTree('');
            }
        }
    }


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •