convert a nested set into data (json/html) for use in jstree

Question

I have a database with a table, in which data is stored as a nested set. The table looks like this:

id      lft rgt name                  depth
0       1   768 ROOT                  0
550300  2   3   external              1
580943  4   5   teachers              1
510000  6   753 company BV            1
213000  7   14  Buying                2
913010  8   9   buying center         3
573100  10  11  buying generic        3
516300  12  13  buying service center 3
513900  15  48  finance               2

The data represents a company structure. I would like to display it with jstree

I thought the best way to do that, was to first place it in a multidimensional array. For that I used this function:

function _formatTreeIntoArray(&$tree, $current_depth = 0)
{
    $formattedTree = array();
    while($leaf = current($tree))
    {
        if($leaf['depth'] > $current_depth)
        {
            $formattedTree[] = _formatTreeIntoArray($tree, $leaf['depth']);
        }
        elseif($leaf['depth'] < $current_depth)
        {
            return $formattedTree;
        }
        else
        {
            $formattedTree[] = $leaf;
            next($tree);
        }
    }

    return $formattedTree;
}

that I found here: https://gist.github.com/taion809/6510212 and slightly modified.

It seems to be doing a pretty good job (Tried the solution in here before, but there I was losing nodes: How do I format Nested Set Model data into an array?)

Now my next challenge it to create a jtree out of it, which should either look like this:

<div id="html1">
  <ul>
    <li>Root node 1
      <ul>
        <li>Child node 1</li>
        <li><a href="#">Child node 2</a></li>
      </ul>
    </li>
  </ul>
</div>

or this:

$('#using_json').jstree({ 'core' : {
    'data' : [
       'Simple root node',
       {
         'text' : 'Root node 2',
         'state' : {
           'opened' : true,
           'selected' : true
         },
         'children' : [
           { 'text' : 'Child 1' },
           'Child 2'
         ]
      }
    ]
} });

I found a function in this topic: PHP Array to jsTree , but since the function that turns the nested set into a array creates many array entries without a name for the key, I get a lot of extra levels in the tree with just a number as name, and without the proper children.

I was wondering how I could solve this, and if there's maybe a better way to achieve my goal.

score 2 · Accepted Answer · answered Feb 24 '17 at 17:13

There are two formats you can use to pass JSON object to jsTree to build tree. I would use the second 'flat' one like below. You will have to build it server-side of course.

[
  { "id": "root", "parent": "#", "text": "ROOT" },
  { "id": "external", "parent": "root", "text": "external" },
  { "id": "teachers", "parent": "root", "text": "teachers" },
  { "id": "companyBV", "parent": "root", "text": "company BV" },
  { "id": "buying", "parent": "companyBV", "text": "Buying" },
  { "id": "finance", "parent": "companyBV", "text": "finance" },
  { "id": "buyingCenter", "parent": "buying", "text": "buying center" },
  { "id": "buyingGeneric", "parent": "buying", "text": "buying generic" },
  { "id": "buyingSCenter", "parent": "buying", "text": "buying service center" }
]

On client side just feed it to the jsTree config:

$('#jstree').jstree({
    core: {
      data: data
    }
})

Check demo - Fiddle Demo

Thanks Nikolay, that structure certainly makes the querying a bit easier, I found a good query in this topic http://stackoverflow.com/questions/659504/is-there-a-simple-way-to-query-the-children-of-a-node to get the results from the db in a format where I get the child and the parent id, that I could then use for jstree. — ErikL, Mar 03 '17 at 11:10

convert a nested set into data (json/html) for use in jstree

1 Answers1

Linked