SitePoint Sponsor

User Tag List

Page 6 of 6 FirstFirst ... 23456
Results 126 to 141 of 141
  1. #126
    Anonymous
    SitePoint Community Guest
    Excellent article which is a great primer on storing hierarchical data.
    However, I ran into a snag using the mptta. Recursion may not be required to query the tree, but it is for building or modifying the tree. I wrote about a different method. I'll post a link as soon as Sitepoint lets me. This method uses the path enumeration model, with a twist. Enjoy.

  2. #127
    SitePoint Member Obsessive's Avatar
    Join Date
    Sep 2008
    Location
    Lithuania
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Andrew View Post
    Also, your solution using the LEFT and RIGHT is simply turning a hierarchical model into a binary try, which will have severe limitations when building a complex tree, such as when a child has multiple parents, and those parents have other multiple children.
    Just like to point out, that Andrew is talking about graphs, while the tutorial was about hierarchical data model, which is a binary tree. Though, there are other kind of models.

    Anyway, great article A while ago I used adjacency model, but now i use b-tree like. It's true that it's harder to understand, but it's worth it, due to the performance gained when retrieving the list.

  3. #128
    Jose Hernandez
    SitePoint Community Guest
    The line

    INSERT INTO tree SET lft=6, rgt=7, title='Strawberry';

    is missing the parent field. Otherwhise, the record has no parent:

    INSERT INTO tree SET lft=6, rgt=7, title='Strawberry', parent='Red';

  4. #129
    SitePoint Member
    Join Date
    Nov 2008
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    As for the issue raised about designs where the child has multiple parents, I believe (it seems to work for me) to modify the rebuild_tree function like:

    rebuild_tree($parent, $left, $mparent)
    . . .
    $right = rebuild_tree($row['title'], $right, $parentid);
    . . .

    mysql_query('UPDATE tree SET lft='.$left.', rgt='.$right.' WHERE title="'.$parent.'" && mparent="'.$mparent.'";');

    Difficult to fully explain in a comment box, but this seems to work for me.

  5. #130
    jim
    SitePoint Community Guest
    I hate to say it, but this method seems to be an outlandish hack. Wouldn't it be much simpler to build a trigger generated 'flattened' table (a table with one record for each node in which the node is a member of)?

  6. #131
    SitePoint Member
    Join Date
    Jun 2009
    Posts
    0
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    good stuff but not emough for us who is not familiar,
    what if i make a cms and customer ads a sub menu then all the left and right numbers have to change, how can we calculate and update other fields,
    this way i can use xml or html too,
    thanks and please respond,

  7. #132
    SitePoint Member
    Join Date
    Jul 2009
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I like that rebuild tree function. Yeah it's a bit expensive, but for something like a category list, even if there's a couple hundred, it's only a one time expense. Really makes the rest a breeze. A custom sort order could also be incorporated as simply as: "SELECT title FROM tree WHERE parent="'.$parent.'" ORDER BY 'sort_order' ASC"

    One question though. If not going about doing it the lazy err easy way, adding isn't that difficult, but what about moving nodes? I'd like to see a solution that's elegant that can switch Yellow and Red. I suppose it would need to look at red lft and yellow rgt and just reorder between. But that could still be lots of work in a large structure. Any good solutions out there?

  8. #133
    Blacksonic
    SitePoint Community Guest
    Great article!!!
    the rebuild function was the most useful, cuz other functions are also readable elsewhere
    in some days i'll make the node moving functions...i'm really impressed with nested set...getting the tree with nested set is 3 times quicker even with this little tree, than the normal way

  9. #134
    SitePoint Enthusiast
    Join Date
    Oct 2005
    Posts
    33
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I had a problem like this awhile ago.

    Here's my solution:
    http://geniegate.com/art/pdf/tree-st...l-database.pdf

    It was kind of nice to be able to do this:

    Code:
    SELECT 
      * 
    FROM 
      directory 
    WHERE 
      lookup_directory_ancestor_path(id) LIKE 'Blah/%';
    And not clutter the program up with a bunch of tree handling stuff.

    I was going to go with flat files at first, but decided I wanted relational after thinking about some of the other aspects (integrity checks).

    You can also create database-side indexes, but in practice, I haven't needed them. (I just created an index, then dropped it, to see if it'd actually work.)

  10. #135
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    how can i find the level?

  11. #136
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,341
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by tudoroiuj View Post
    how can i find the level?
    which level?

    please show your table design
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  12. #137
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by tudoroiuj View Post
    how can i find the level?
    My apologies if this has already been suggested earlier, but this thread is too long for me to go back looking through all the posts.

    To store hierarchial data in a database I would recommend using The Nested Set Model. This link also has some very useful sql scripts on extracting hierarchy information from the database.

    I have posted some sql scripts on how to move hierarchial nodes.

  13. #138
    SitePoint Member
    Join Date
    Oct 2010
    Posts
    1
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Nice tutorial, let me ask you something, how can I create an automated hierarchical tree of 5 or more, for example:

    Electronics
    dvd
    eye cleaner
    movies: horror
    movies: drama
    movies: comedy
    movies: thriller
    cd player
    camera
    recorder
    tv

    I'm triying to do something like this with your sample, without no success.
    Thanks for your help!

  14. #139
    SitePoint Member said_fox's Avatar
    Join Date
    Oct 2004
    Location
    Kafr Sakr, Egypt
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Is there any third approach for dealing with hierarchical data?

  15. #140
    Non-Member Kalon's Avatar
    Join Date
    Aug 2010
    Location
    At my computer
    Posts
    2,012
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by bergmp View Post
    Nice tutorial, let me ask you something, how can I create an automated hierarchical tree of 5 or more, for example:

    Electronics
    dvd
    eye cleaner
    movies: horror
    movies: drama
    movies: comedy
    movies: thriller
    cd player
    camera
    recorder
    tv

    I'm triying to do something like this with your sample, without no success.
    Thanks for your help!
    I'm not sure what you mean by "automated" hierarchal tree.

    What I normally do is start off with a root node in the database. I then give the user a form to add nodes with as many sub-nodes as they like. The form prompts for a new node name and a reference node to add the new node to. When the form is submitted I then run sql scripts as in or similar to the tutorial to add the node.

    The sql scripts add the node name and give it appropriate left and right values according to where it is being added in the tree. The nodes to the right of the added node then have their left and right values adjusted accordingly.

  16. #141
    SitePoint Enthusiast doddsey_65's Avatar
    Join Date
    Sep 2010
    Location
    North East UK
    Posts
    77
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    sorry to revive an old topic but couldnt find help elsewhere. I am using the preorder tree traversal for my forum and i need to display the subforums under the forum names as a list like many ohter forums do. unfortunatly i dont know how to go about it. anyone have any ideas?


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
  •