Love Fuel?    Donate

FuelPHP Forums

Ask your question about FuelPHP in the appropriate forum, or help others by answering their questions.
Getting nested tree of the result
  • I'm having a table 'categories' with fields (id, name, parent).

    I would like to get nested tree results of it.

    Eg. I select all results with parent = 0 (starting with parent 0 as a root):

    $categories = Model_Categories::find('all', array('where' => array('parent' => 0)));

    Now, I would like to get childrens of category with id 3. Like:

    foreach($categories as $category)
    {
    if ($category->id === 3)
    {
    // do something with childrens
    $category->children(); // I want this to display results of rows with parent set to 3
    }
    }

    Is this possible with ORM? How can I achieve this?

  • Use the nestedsets model: https://fuelphp.com/docs/packages/orm/model/nestedset.html. It has all that functionality built-in, and more.
  • Tried it, but failed. It always returns only the first parent (0). I think this is incompatible with my table structure. 
  • Possible. From the little information you have given, I think your table only uses a parent pointer. Which isn't really sufficient for a nested set, as you don't have a children list, which means next() and previous() can never work properly, and since no list means no position, insert before and after operations will never work as well.

    So either write a migration to convert your table format to something compatible with Nested Sets (the requirements are in the docs), or write your own custom implementation.
  • Yep, apparently I'm missing a `tree_id` field, however I don't really understand what it's used for? 

    left_id would be `id` in my case,
    right_id would contain `parent` in my case

    What should tree_id contain then? Overall tree number? Still doesn't works even with that.

    Always returns: image

    My current table schema and model settings: image

    Database rows:

    image
  • tree_id is not required, it is used when the table contains multiple trees.

    But you can't just swap 'id' and 'parent' by 'left' and 'right', the two systems are completely different. You will have to create a migration that converts the table layout.

    Create a function that accepts a parent id and a tree node object, and which runs a query to select all rows from the old table that have the given parent id. It then loops over the results, and for each result, adds the row as a child of the tree node object passed, then recurses with the id of the row as parent id, and the newly inserted child as tree node.

    Then in the new table, you need to create a nested set root node, and when done, call your function with parent id 0, and the object of that root node as tree node.

    This will convert your parent/child table to a nested set table.
  • In my opinion it's a bit pointless to use nestedset model in case of other table design, this is a overkill. 

    I decided to use original table schema for nestedset.

    Hence 2 more questions:

    1)

    I saw that lots of queries are being run for a simple, 4 tree nested set. Any idea if I can cache the results forever and refresh them only while adding a new category? Or I rather should cache what I want to build with the tree (HTML list)?

    2)

    How can I sort the tree items?

    I build the HTML ul from them like:

    <ul>
    <li>
    Root
         <ul>
    <li>Children of root</li>
    <li>I want to be on the top</li>
    <li>I want to be second</li>
         </ul>
    </li>
    <li>Another root with no children</li>
    </ul>

    Any idea, how can I sort childrens, eg. by name? 
  • I told you what the Fuel way of doing things are.

    Since I have no clue what you build, and what your code does, it's a bit difficult to comment on either of them.
  • Could you answer my questions then? These refer to the base nested set model.
  • No, because I don't know your code.

    As for the sorting, the Fuel NestedSets architecture has a previous/next pointer design, which means every node has a fixed place in the tree. If you want to sort that, you need to fetch every branch of the tree individually, sort them, and store the result in a custom structure (as the sort would break the pointers).

    See the picture in the docs for a visual on how the pointer system works.
  • My code is simple.. let's assume I fetch the whole tree:

    Model_Categories::forge()->roots()->get();

    This returns array with the roots, thats what I want to cache.

    Or the individuals:

    Model_Categories::find(10);

    This one would return an object by primary key. 

    I'm asking for a way to cache those results so the tons of queries wouldn't affect my database I/O on each page load.



    Regarding sorting, thanks. I thought that there is a way to sort the during fetch time. 
  • If you want to minimize the interaction with the DB, you could look at the dump_tree() method, which can return the entire tree in a flat array or in an object tree structure, depending on your requirements.

    We use this a lot for generating menu's, etc.
  • Well, dump_tree() method runs same queries, as I would direct all ->children() on category:

    foreach(Model_Category::forge()->roots()->get() as $category)
    {
    Debug::dump($category->dump_tree(TRUE)->children);
    }

  • Yeah? If you want the tree structure, you'll have to. I thought the question was "can I retrieve the tree so I can cache it"?

    If you just want a flat list, you can also use $category->decendants()->get() which gives you all objects under the root with a single query. Obviously, that returns a flat list, so you need to put more effort in yourself if you actually want to use it.

    btw, I checked the code for dump_tree(), and it does exactly that, which runs a single query per tree. So about which "same queries" are you exactly talking? That should should only run n+1 queries, where n is the number of trees in the table.

    Your link confirms that, according to that output, you have 6 different trees in that table, so it runs 7 queries, one to get the list of tree id's, and one for each tree.
  • I did mention that, because your solution was to use dump_tree() method (to minimalize interaction with the DB) but it turns out that even when I use this the standard way which is:

    foreach(Model_Category::forge()->roots()->get() as $category)
    {
           $category->children()->something[..]
    }

    It would return exact the same number of queries. 

    So, to make it clear, I can not cache the whole tree so I could then re-use it (to bypass n+1 queries)?


    Btw. can I somehow dump tree of the whole table? 

    I'm currently having this trees:

    Tree1
    • Child1
    • Child2
    • Child3
    • Child of child1
    Tree2
    • Child1
    • Child2
    • Child3
    Tree3
    • Child1
    • Child2
    • Child3
    When I run Model_Category::forge()->roots()->get() I get the array with the size of the trees, however, I would like to get the output simiar to the dump_tree(). However, dump_tree() can only be run on tree-selected nodes so I have to select some tree in order to access it's children and so on. I would like to have exact the same array, but with childrens of all trees already.

    I need this to generate the HTML unordered list of my trees, something like you did in this snippet: https://bin.fuelphp.com/snippet/view/HZ but, you're doing it for individual node and I would like to make it for all nodes, so they will be condensed within one <ul> like:

    <ul>
           Tree1
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
           Tree2
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
           Tree3
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
    </ul>
  • There is no "whole" tree, there are 6 different trees, which just happen to be stored in the same table?

    So you have 1 query to get the id's of the trrees, and 1 query for each of the trees. making n+1. And as with any other object, you can use the Cache class to cache them, why not? Or do you mean something else with "cache"?

    If you want them to be part of a single tree, you have to create it as such.

    It looks like you have misunderstood the concept of multiple trees and the tree_id.
  • Thanks for the cache explanation.

    Regarding building HTML unordered list, what function can I use to achieve this?

    I was trying to recurse over a single tree (like you did in your here: https://bin.fuelphp.com/snippet/view/HZ)

    But since I got multiple trees, it's of course wrapping every tree with <ul> like:

    <ul>
           <li>Tree1</li>

           <ul>
                  [childrens...]
           </ul>
    </ul>
    <ul>
           <li>Tree2</li>

           <ul>
                  [childrens...]
           </ul>
    </ul>
    and so on...

    But I want this:

    <ul>
           <li>Tree1</li>
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
           <li>Tree2</li>
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
           <li>Tree3</li>
           <ul>
                  <li>Child1</li>
                  [..]
           </ul>
    </ul>

  • I know I can wrap my function with another, but I think there is an easier and prettier way. 
  • If you don't want to <ul> in there, remove it from your function, and add them only before and after you call our function?

    Our closure is written specifically for the theme system we use, you will have to adapt the HTML generation to whatever you use.
  • Look, I'm looking for a simple solution to 'clone' trees from database table directly into HTML list as they are. 

    I'm a bit confushed, if I'm doing it 100% the way it has to be done because it looks a bit dirty to me, see:

    $buffer = '<ul>';

    foreach(Model_Category::forge()->roots()->get() as $root){
    $buffer .= Model_Category::buildTree( $root->dump_tree(TRUE) );
    }

    $buffer .= '</ul>';

    Are you sure, it can't be done better? 
  • You could ask yourself why you have created 6 seperate trees, while it seems clear you want them all to be part of the same tree? 

    Any particular reason you made 6? Seperate trees are not supposed to have any relation...
  • I thought that I need to create one tree for one category.

    Should I have created one dummy, main tree and then nest all categories inside it or you have something else on your mind? 
  • HarroHarro
    Accepted Answer
    That depends on how you use it. 

    If you store multi-level menu's (for things like drop-downs and so), it is logical that each menu is a separate tree, as they are not related to each other, and you use them seperately.

    If you look at for example the product category list in a webshop, it is usually used and displayed as a single tree, all info belongs together.

    So if that is your intention, I would create a single tree, with a root node you only use as "anchor" (dummy if you will), and with all your main categories as children of that root node. And then write your HTML generator accordingly, so that it starts recursing with the root node, but don't include it in the output.
  • Yeah, I have modified my code to a single tree type and it works very well. Only one query is being run, to get everything in one go.

    Tree looks as follows:

    image
    image

    Thanks so much for being patient with me.

    If someone would be interested, to skip the 'dummy' [main] tree do the following:

    Model_Tree::forge()->roots()->get_one()->dump_tree( TRUE )->children

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

In this Discussion