What type of relation is this scenario?

[co.ador]

for instance if FRUIT is call as below then only the nodes in the level of FRUIT will display Which are node FRUIT itself and node MEAT.

$hierachy = new hierachy;
$iterator = new RecursiveIteratorIterator(new recursiveArrayIterator($hierachy->Somefunction('FRUIT')));
try {
    foreach($iterator as $key=>$value)
        {
        echo $value.'<br />';
        }
    }
catch(Exception $e)
    {
    echo $e->getMessage();
    } ?> 

[co.ador]

"SELECT name
FROM categories
WHERE left_node = right_node -7"

I tried the obove query but it only queried one level it doesn't apply to all the level because not in all the level there is a difference of 7, it only worked for the first level of FRUIT and MEAT.

[r937]

Took me (and everyone, I think) about 20 posts and 15 minutes of reading to figure out what I was even dealing with.

some of us still haven't caught on

this thread also illustrates that the nested set model can be difficult to grasp, and often difficult to write SELECT queries for (to say nothing of INSERTs, UPDATEs, and DELETEs)

[transio]

Yeah, that's why I avoid nested set wherever possible... it's one of those things that's cool on paper but a mess in practice. Plain old recursive tree structure will work 99% of the time, and is simple(r) to implement.

[co.ador]

I am afraid if using any other model I will have to structure a table for every node... lol.

What is your substitute for this model then?

recursive model?

[StarLion]

NSM is great for when you want to retrieve a chain to/from a node and the root (or the sweeping children of the chain).
When you start requiring depth calculations you'll run into issues.

That said, it's still perfectly doable with PHP. Wasteful, but doable.

SELECT name,lft,rgt..... (as before.. select all children of target node.)

while($row = $res->fetch_array()) {
  if(!isset($targ)) {
   $targ = $row['lft']+1; // First row = the node we searched from. First child's lft = lft+1.
  }
  if($row['lft'] == $targ) { //This is a child of the searched node.
    echo $row['name'];
    $targ = $row['rgt']+1; //next child's lft = this child's rgt + 1.
  }
} 

[TheRedDevil]

There is cases where the "Nested Set Model" is a good idea, and others where it is not. To be honest I have not read all the posts in the thread, as many was very incoherent and it was very difficult to understand what the op wanted to do. So I am not certain if this model is a good idea for his use, but he should be able to figure out that himself I think, once he understands the concept.

The problem with the NSM, is that it is a difficult model to grasp and understand. From the look of co.ador's posts, it seems like it is this that is the problem.

I would recommend that you (co.ador) read over this article regarding the subject.
MySQL :: Managing Hierarchical Data in MySQL

It should give you a better understanding of how it works, the "problem" with NSM is not to pull data, but the fact that every time you do a change something you need to redo the left/right values.

[r937]

I would recommend that you (co.ador) read over this article regarding the subject.
MySQL :: Managing Hierarchical Data in MySQL

"yeah, next time, maybe do a little research" (YouTube - Geico Cavemen Apology.)

you should have read the thread, this link was posted long ago

[TheRedDevil]

Ah yea, saw it now. My bad.

I just saw all the references was to the Sitepoint article about the topic, so I assumed it had not been posted.

Ive read over the posts now (should have done that the first time )

What Im left with, is that I am not sure if the op wants/need the Nested Set Model at all. Especially after post 51.

If the goal is to be able to pull both for example:
Fruit, Red, Cherry, Yellow
or
Red, Cherry

And in addition you want to be able to pull:
Fruit, Meat (as explained in post 51)

Then go with both models, use both the Adjacency List Model and Nested Set Model. If you need both those options in my examples, using both models is the best of two worlds and it is not difficult to set up.

[transio]

There is cases where the "Nested Set Model" is a good idea, and others where it is not.

I agree... NSM is good where 1) there is a high number or potentially infinite number of generations of data (I'm talking in practice, not in theory - most models don't make it past 5 generations), and/or 2) when SELECT performance is an absolute priority (to the detriment of development time).

If either of these conditions is met, NSM is a great solution. It is better-performing all-around, though takes significantly more time to develop an app around.

[co.ador]

@TheRedDevil, I was thinking about how I will have to change the left and Right values every time it is updated, doing that manually would be a pain. But in the mysql article I skinned through the UPDATE and INSERT part for this NSM and it seems they have some solution for that, i haven't get into that part in details because I still figuring out displaying values in the same level, a level above and below the tree. You have also mention about using Adjacency List to display values like Fruit and Meat which are in the same level. Yes using both model would be a great solution as you mention and I came across as I was thinking before.

@Transio the nested model would be great for this scenario then because there will be a high potential generation of data, But what I indeed thought about was maybe the issues of how it would be possible to UPDATE all the right and left fields when one memeber is added at any given part in the three. Because if one node is added I think then all the right and left will have to change to reposition the data.

in the mysql tutorial they have an update option for that which I will take on later but I can't wait to know how they have figure that out.

@Starlion, great still working on that option of the while loop.

[StarLion]

It works as a patch, co.ador, but it is -highly- wasteful the further up and the bigger the tree is. ( If your target node has 1 child, but that child has 500 children, you've pulled 501 records to find 1 of them).

[oddz]

Actually, the best approach would be to use a database vendor that provides native support for hierarchical data if this a key part of the application. Otherwise, I would say use Adjacency list w/ caching as its far easier to maintain and understand.

[co.ador]

it is complicated in deed, getting results but still have to test it in the INSERT part of it, to see how it is going to work.

[StarLion]

Actually, the best approach would be to use a database vendor that provides native support for hierarchical data if this a key part of the application. Otherwise, I would say use Adjacency list w/ caching as its far easier to maintain and understand.

Please to give me 'far easier' select query for root-to-node navigation in Adjacency list with infinitely many depth levels.

[transio]

Please to give me 'far easier' select query for root-to-node navigation in Adjacency list with infinitely many depth levels.

With all due respect, he said "maintain and understand", not "query".

It's give-and-take. NSM is easier and faster to query, but a pain to maintain.

[StarLion]

Maybe i'm missing where you get 'pain to maintain' from...

Insert a new node as sibling:

SELECT rgt FROM table WHERE name = "Yellow";

UPDATE rgt = rgt + 2 WHERE rgt > $YellowRgt;
UPDATE lft = lft + 2 WHERE lft > $YellowRgt;
INSERT INTO table(name,lft,rgt) VALUES('Green',$YellowRgt+1,$YellowRgt+2);

... i personally dont call that a pain? Perhaps Adjacency can do it in a single query (Two if you're storing adjacency by ID and need to retrieve the ID), but still wouldnt call that a 'pain'.

Deleting a node without destroying it's children would be a (minor) pain (as you'd have to designate a new parent for the subtree), but if you destroy the subtree at the same time, it's cake too...

SELECT lft,rgt FROM table WHERE name = "Yellow";
DELETE FROM table WHERE lft BETWEEN $Yellow.lft AND $Yellow.rgt
UPDATE table SET lft = lft - (Yellow.rgt - $Yellow.lft) WHERE lft > $Yellow.rgt;
UPDATE table SET rgt = rgt - (Yellow.rgt - $Yellow.lft) WHERE rgt > $Yellow.rgt;

... done.

[r937]

it's "cake" only once you know how

let's do a quick review... how many posts in this thread, just to teach the guy how to do nested set selects which are even easier than cake?

[StarLion]

it's "cake" only once you know how

let's do a quick review... how many posts in this thread, just to teach the guy how to do nested set selects which are even easier than cake?

How many posts in this thread will it take to teach him how to do adjacency queries for all the different types of data he wants to retrieve? Lets find out. Stay tuned to "As The Thread Turns".

[r937]

don't change the subject, we're talking about how difficult nested sets are to learn

adjacency lists might also be hard to learn, but not nearly as much

and in any case, you haven't finished teaching him nested set SELECTs, never mind the other stuff

so there

[transio]

"Cake"? How is 4-queries-to-insert cake? And that's just one scenario... show all the other relationship scenarios, too... inserting a node mid-tree, new top-level-parent, etc. All complicated to maintain. Not saying complicated to understand.... but there are added dimensions of complexity to make this system work. And if you want it to be "enterprise", it all has to be transacted to ensure your data doesn't get corrupted (e.g. update succeeds, insert fails), and you'll have to create sprocs to neatly wrap everything in a high performance solution... meaning you'll want to then use prepared statements in your code... all in all it's a more complicated solution... better, yes, but probably about 3x the development time of the "adjacency" (simple parent-child) model.

[StarLion]

I'm still waiting for you to display the "simple parent-child" root-to-node (and the inverse too) on an infinitely deep adjacency tree, transio... if i have to show all the relationship scenarios, you could try displaying 1 at least.

Inserting a node mid-tree? You mean... replace the parent of a subtree with a new node between the two? Cause i'm fairly sure you're gonna need at least 3 queries for that with adjacency too (SELECT current 1st-level-children, INSERT node, UPDATE all 1st-level-children with new parent)
SELECT lft,rgt FROM table WHERE name = "ParentName"
UPDATE table SET rgt = rgt + 2 WHERE rgt > $ParentLeft
UPDATE table SET lft = lft + 2 WHERE lft > $ParentLeft
INSERT INTO table(name,lft,rgt) VALUES ("New SubParent",$ParentLeft+1,$ParentRgt+1)

New top level parent? As in new root?
UPDATE table SET lft = lft + 1, rgt = rgt + 1
SELECT rgt FROM table WHERE lft = 2;
INSERT INTO table(name,lft,rgt) VALUES ("NewRoot",1,$oldtopright + 1);

that was taxing indeed.

As I have clearly stated. When you start requiring depth indicators, you're going to have some issues. I've never said NSM was the end-all solution. But you're rather quick to blast it without producing anything to the contrary.

[transio]

I'm still waiting for you to display the "simple parent-child" root-to-node (and the inverse too) on an infinitely deep adjacency tree, transio... if i have to show all the relationship scenarios, you could try displaying 1 at least.

I never said it was simple on an infinite tree... I said that NSM is better in cases of more than 5-10 levels, or when select performance is required to be optimal. I'm not a proponent of one vs. the other, just simply stating the obvious that each has its benefits and detractors. I've found that most real-world hierarchy models are limited in levels of recursion, but I also understand that there are cases where they're not, and I agree that NSM is the better option for those cases, but even though querying the data will be simpler and faster, it's not arguable that "maintaining" (inserting/updating) the data is still more work with NSM. Not sure how that equates to "blasting" it... it's just my opinion that it's usually not worth the extra work, and prefer the simpler model for most real-world cases.

[co.ador]

I think the party just started

/* Get Fruit */
SELECT P.name
FROM categories AS C
    INNER JOIN categories AS P
        ON C.parent_id = P.category_id
WHERE C.name = 'red';        
 
/* Get Cherry */
SELECT C.name
FROM categories AS C
    INNER JOIN categories AS P
        ON C.parent_id = P.category_id
WHERE P.name = 'red';        
 
/* Get Yellow */
SELECT C2.name
FROM categories AS C1
    INNER JOIN categories AS C2
        ON C1.parent_id = C2.parent_id
 WHERE C1.name = 'red';       
 
/* Get FOOD */
SELECT G.name
FROM categories C
    INNER JOIN categories AS P
        ON C.parent_id = P.category_id
    INNER JOIN categories AS G
        ON P.parent_id = G.category_id
WHERE C.name = 'red';        
 
 
ALL nodes WITH same grandparent AS red EXCEPT the siblings OF red.
 
/* GET Beef */
SELECT
    C2.name
FROM categories C
    INNER JOIN categories AS P
        ON C.parent_id = P.category_id
    INNER JOIN categories AS P2
        ON P2.parent_id = P.parent_id AND
           P2.category_id <> P.category_id
    INNER JOIN categories AS C2
        ON P2.category_id = C2.parent_id
WHERE C.name = 'red';

The fish hook will be red, and will pull an element of the tree according to the query requested.

Now we can say there are two ways or maybe more of going through a tree such as Nested and this new approach I just brought giving credit to Mikael Eriksson in stuckoverflow, why? I think Nested should have their advantages and disadvantages as well as the way I just showed you above in this post.

I wonder where Nested has advantages that this way might not or vice verse. what advantages each one has as the moment for INSERT CREATE, DELETE a child and leave the parent, and what that child is going to take as a parent. so many questions risen in terms of comparisons, advantages, disadvantages, different uses in between this ways that in deep will bring similar results.

[StarLion]

DELETE and INSERT-mid-tree is going to cause the 'hassle' of adjacency (which is what you're describing here, and defining 'hassle' as more than 1 query, which is apparantly our definition), in that you have to sweep through and change all children thereof. Adjacency is also limited in that it cant recurse - for example.

Taking your tree. What if you didnt know if Red had a grandparent? or a great-grandparent? or 20 levels more of tree above/below it?
You could find out, sure, but you'd have to run query after query until you got a null set. And those queries get bigger and bigger for each level (New Inner Join per level... if your tree was 20 levels big, thats a really, really big query, loading the same table 20 times.)

If you -know- -exactly- how many levels the tree is (and you dont, because you may have 3, or 4, or 5 from what you were saying back in the original posts) or you only want to get information about 1 level at a time, adjacency is great. Quick and straightforward.

NSM is great when you want to be able to get information about the chain of nodes related to the target node - all parents of, all children of, etc. It is slightly more to understand conceptually, and as has been argued here, more difficult to maintain. If your data wont be changing all that often, then that argument is kind of moot since you'll be spending 99% of your time SELECT'ing.