Hieratic Category Query

[eluser]lightnb[/eluser]
I have a hieratic category scheme in my "categories" table.

There are three fields: Name, ID, parent ID

Parent IDs of 0 are root elements.

How can I get them to display as a hieratic list- Eg:


Animals

-Domestic
---Dog
---Cat

-Wild
---Lion
---Bear

[eluser]nmweb[/eluser]
It's a complex issue. If the table is small just load the entire thing and have php handle it. Otherwise you might want to look at the MPTree library in the wiki.

[eluser]Pygon[/eluser]
This should answer your questions:

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

[eluser]lightnb[/eluser]
Thanks guys.


I read through the articles and the wiki, and I'm still confused as to how this works. It seems really complicated, for something thats very simple in theory.
The adjacency list method makes sense, since I can have a drop down box to easily change the parent of the category, and everything will magically work. the other one (with numbers on both sides) seems like a pain to have to update?

All the big forum packages have sub-categories and so do the shopping carts. Do they use a different, perhaps simpler method, to emulate the tree behavior?

Would the task be simpler by reducing the number of levels to a finite number? I would love to have the power of infinite levels, but not at the expense of processing power /memory /rendering time.

[eluser]lightnb[/eluser]
Ok, so I'm going with the LeftID, RightID one, since that's what phpBB uses, and it seems to work.

I can get it to read right with:

Code:

$this->db->select('node.ForumCategoryName, (COUNT(parent.ForumCategoryName) - 1) AS Depth, node.ForumCategoryID, node.ForumCategoryType As Type');

$this->db->from('Forums_Categories AS node, Forums_Categories AS parent');
        
$Where = "node.LeftID BETWEEN parent.LeftID AND parent.RightID";
$this->db->where($Where);
        
$this->db->group_by('node.ForumCategoryName');
$this->db->order_by('node.LeftID');

$Query = $this->db->get();

But trying to add a new record is a disaster. I don't know if it's the query, or the stupid db object syntax, but it's doing all sorts of strange things.

Assume $NewCatName is a string of the catergory name to add, and $ParentCat is the ID of the parent object that the new object will belong to.

I'm using:

Code:

$this->db->select('@myRight := RightID');
$this->db->from('Forums_Categories');
$this->db->where("ForumCategoryID = '$ParentCat'");
$Query = $this->db->get();

$data = array('RightID' => 'RightID + 2');
$this->db->where('RightID > @myRight');
$this->db->update('Forums_Categories', $data);
      
$data = array('LeftID' => 'LeftID + 2');
$this->db->where('LeftID > @myRight');
$this->db->update('Forums_Categories', $data);
      
$data = array('ForumCategoryName' => $NewCatName, 'LeftID' => '@myRight + 1', 'RightID' => '@myRight + 2');

$this->db->insert('Forums_Categories', $data);

[eluser]Rick Jolly[/eluser]
To give you an idea of adjacency list performance, have a look at the menu for this site: http://eddies.com. It's a huge menu, and it uses a nice, simple adjacency list.

[eluser]lightnb[/eluser]
I'm not sure how to make either method work... So the best option is whichever I can get working first.

I have "read" working fine on the left-right one. I just can't get the insert function to work.

The article says to do this (in "normal" mysql):

Code:

LOCK TABLE nested_category WRITE;
SELECT @myRight := rgt FROM nested_category
WHERE name = 'TELEVISIONS';
UPDATE nested_category SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE nested_category SET lft = lft + 2 WHERE lft > @myRight;
INSERT INTO nested_category(name, lft, rgt) VALUES('GAME CONSOLES', @myRight + 1, @myRight + 2);
UNLOCK TABLES;

But it's not translating well into $this->DBish.

[eluser]nmweb[/eluser]
There is a perfect working library to handle such matters in the wiki. MPTT, or MPTTree or something is it's name. It handles all the lft, rgt stuff + scope so you can have multiple trees in one table. It can even do ORM for you.

Adjacency lists might bring about problems with performance but proper caching or just loading the entire table and have php handle the parents/childs you can cut down on the queries.

[eluser]lightnb[/eluser]
I've downloaded and installed the demo for the Nested Sets Library, but there seems to be some bugs.

If you attempt to move the root level category to a child position, the entire tree disappears. The categories are still in the database, but their left/right numbers go crazy.

[eluser]nmweb[/eluser]
I think moving the root anywhere results in tree corruption. It's hugely problematic to remove the root, the library should set its first child in its place as a solution but easier is too disable moving roots altogether.