SitePoint Sponsor

User Tag List

Page 3 of 4 FirstFirst 1234 LastLast
Results 51 to 75 of 81
  1. #51
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)


    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.

    PHP Code:
    $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();
        } 
    ?> 

  2. #52
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code SQL:
    "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.

  3. #53
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by transio View Post
    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)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #54
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  5. #55
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  6. #56
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.)

    PHP Code:
    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.
      
    }


  7. #57
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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.

  8. #58
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by TheRedDevil View Post
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #59
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    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.

  10. #60
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by TheRedDevil View Post
    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.

  11. #61
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    @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.

  12. #62
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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).

  13. #63
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,182
    Mentioned
    16 Post(s)
    Tagged
    4 Thread(s)
    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.
    The only code I hate more than my own is everyone else's.

  14. #64
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  15. #65
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    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.

  16. #66
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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.

  17. #67
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.

  18. #68
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #69
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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".

  20. #70
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #71
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    "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.

  22. #72
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.

  23. #73
    Non-Member
    Join Date
    Apr 2004
    Location
    Miami, FL, USA
    Posts
    448
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by StarLion View Post
    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.

  24. #74
    SitePoint Wizard co.ador's Avatar
    Join Date
    Apr 2009
    Posts
    1,054
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I think the party just started



    Code SQL:
    /* 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.

  25. #75
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.


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
  •