Nested Set Model

As discussed in previous chapter Configuration one of the important part is the treeReader property. Configuring this properly and understanding the basic concept of the nested set model will make your life easy.

The default treeReader Configuration when we use tree grid with nested set model is:

treeReader : {
    level_field: "level",
    left_field:"lft",
    right_field: "rgt",
    leaf_field: "isLeaf",
    expanded_field: "expanded"
}

The treeReader automatically extends the colModel with these fields, added and hidden at end of the colModel. Data returned from the server now needs to include information for these fields for constructing the tree grid. The treeReader can be extended so that the fields match your requirements.

Another option that can be changed is tree_root_level. By default this has value 0. This option tell which level has the root element.

After we configure the reader we need to know what we post to the server in order to load the child nodes properly? In case of auto lading tree nodes we post the following parameters - also the postData array is extended. See here

postData : {
   ...
   nodeid:rc.id,
   n_left:rc.lft,
   n_right:rc.rgt,
   n_level:rc.level,
   ...
}

• nodeid contain the id of the currently expanded record
• n_left contain the left value of the currently expanded row
• n_right contain the right value of the currently expanded row
• n_level contain the level value of the currently expanded row

In order to understand the the process of configuring the tree grid here we provide full example:

Let us suppose that we have an account table where some accounts are children of the main accounts and some accounts have no child account. In the Nested Set model the table can look like this

account_id, name, account_number, Debit, Credit, Balance, lft, rgt

where:

• account_id is the uniquie id of the account (in our grid this should be the rowid)
• lft indicates the left_field, and
• rgt indicates the right_field

In MySQL terms this table can be represented as

CREATE TABLE accounts (
  account_id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(30) NOT NULL,
  acc_num VARCHAR(10) NULL,
  debit DECIMAL(10,2) DEFAULT '0.00',
  credit DECIMAL(10,2) DEFAULT '0.00',
  balance DECIMAL(10,2) DEFAULT '0.00',
  lft INT(11) NOT NULL,
  rgt INT(11) NOT NULL,
  PRIMARY KEY  (`account_id`)
);

Let's add some data:

INSERT INTO accounts VALUES (1, 'Cash', '100', 400.00, 250.00, 150.00, 1, 8);
INSERT INTO accounts VALUES (2, 'Cash 1', '1', 300.00, 200.00, 100.00, 2, 5);
INSERT INTO accounts VALUES (3, 'Sub Cash 1', '1', 300.00, 200.00, 100.00, 3, 4);
INSERT INTO accounts VALUES (4, 'Cash 2', '2', 100.00, 50.00, 50.00, 6, 7);
INSERT INTO accounts VALUES (5, 'Bank''s', '200', 1500.00, 1000.00, 500.00, 9, 14);
INSERT INTO accounts VALUES (6, 'Bank 1', '1', 500.00, 0.00, 500.00, 10, 11);
INSERT INTO accounts VALUES (7, 'Bank 2', '2', 1000.00, 1000.00, 0.00, 12, 13);
INSERT INTO accounts VALUES (8, 'Fixed asset', '300', 0.00, 1000.00, -1000.00, 15, 16);

With this information we can now construct the treeGrid.

<script>
...
jQuery("#treegrid").jqGrid({
   treeGrid: true,
   treeGridModel: 'nested',
   ExpandColumn : 'name',
   url: 'server.php?q=tree',
   datatype: "xml",
   mtype: "POST",
      colNames:["id","Account","Acc Num", "Debit", "Credit","Balance"],
      colModel:[
         {name:'id',index:'id', width:1,hidden:true,key:true},
         {name:'name',index:'name', width:180},
         {name:'num',index:'acc_num', width:80, align:"center"},
         {name:'debit',index:'debit', width:80, align:"right"},      
         {name:'credit',index:'credit', width:80,align:"right"},      
         {name:'balance',index:'balance', width:80,align:"right"}      
      ],
   height:'auto',
   pager : "#ptreegrid",
   caption: "Treegrid example"
});
...
<script>

Since jqGrid currently does not support paging, when we have a treegrid the pager elements are disabled automatically.

Loading all the nodes at once is an approach used when we have relatively few elements in the data table. To do this, our single SQL can be

SELECT
    node.account_id,
    node.name,
    node.acc_num,
    node.debit,
    node.credit,
    node.balance,
    (COUNT(parent.name) - 1) AS level,
    node.lft,
    node.rgt
FROM accounts AS node,
accounts AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

In Nested Set model, determining if the node is a leaf is easy: this is just comparison of rgt = lft+1.
Now we are ready to prepare our server side code. Below are examples in PHP and MySQL, xml and json. Examine the code to see where additional elements are added.

<?php
// this query determines the total number of records in the tree (can be omitted)
$result = mysql_query("SELECT COUNT(*) as count FROM accounts");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];
// the actual query
$SQL = "SELECT "
."node.account_id, "
."node.name, "
."node.acc_num, "
."node.debit, "
."node.credit, "
."node.balance, "
."(COUNT(parent.name) - 1) AS level, "
."node.lft, "
."node.rgt "
."FROM accounts AS node, "
."accounts AS parent "
."WHERE node.lft BETWEEN parent.lft AND parent.rgt "
."GROUP BY node.name "
."ORDER BY node.lft";
 
$result = mysql_query( $SQL ) or die("Couldn’t execute query.".mysql_error());

<?php
if ( stristr($_SERVER["HTTP_ACCEPT"],"application/xhtml+xml") ) {
    header("Content-type: application/xhtml+xml;charset=utf-8");
} else {
   header("Content-type: text/xml;charset=utf-8");
}
 
$s = "<?xml version='1.0' encoding='utf-8'?>";
$s .=  "<rows>";
$s .=  "<page>1</page>";
$s .=  "<total>1</total>";
$s .=  "<records>".$count."</records>";
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
   $s .=  "<row>";         
   $s .=  "<cell>". $row[account_id]."</cell>";  // the id of the row is setted in colmodel, no need to put id in row
   $s .=  "<cell>". $row[name]."</cell>";
   $s .=  "<cell>". $row[acc_num]."</cell>";
   $s .=  "<cell>". $row[debit]."</cell>";
   $s .=  "<cell>". $row[credit]."</cell>";
   $s .=  "<cell>". $row[balance]."</cell>";
   $s .=  "<cell>". $row[level]."</cell>"; // level element
   $s .=  "<cell>". $row[lft]."</cell>"; // left_field element
   $s .=  "<cell>". $row[rgt]."</cell>"; // right_field element
   if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false'; // this determines if the node is aleaf
   $s .=  "<cell>".$leaf."</cell>"; // isLief element
   $s .=  "<cell>false</cell>"; // expanded element - we set by default t false
   $s .=  "</row>";
}
$s .=  "</rows>";      
 
echo $s;
?>

header("Content-type: text/html;charset=utf-8");
$response->page = 1;
$response->total = 1;
$response->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false';
    $response->rows[$i]['cell']=array($row[account_id],
        $row[name],
        $row[acc_num],
        $row[debit],
        $row[credit],
        $row[balance],
        $row[note],
        $row[level],
        $row[lft],
        $row[rgt],
        $leaf,
        'false'
    );
    $i++;
} 
echo json_encode($response);
?>

When we have a relative large data set with a deep structure, is is better to load the data when we need it, i.e. only when a parent is clicked on do we retrieve the child records. So first we display only the root elements; when a root elemnt is clicked on, the grid automatically detects that there is no data and tries to load the needed information by passing the needed parameters to the server. This is where the level_field and isLeaf field are so important.
In this case we can use our previous query producing only the elements at the requested level. (This query can be optimized, but this is out of scope for this explanantion).

<?php
$ADDWHERE = '';
$node = (integer)$_REQUEST["nodeid"];
// detect if here we post the data from allready loaded tree
// we can make here other checks
if( $node >0) {
   $n_lft = (integer)$_REQUEST["n_left"];
   $n_rgt = (integer)$_REQUEST["n_right"];
   $n_lvl = (integer)$_REQUEST["n_level"];			
   $ADDWHERE = " AND lft > ".$n_lft." AND rgt < ".$n_rgt;
} else { 
   // initial grid
   $n_lvl =0;
}
$SQL1 = "SELECT "
."node.account_id, "
."node.name, "
."node.acc_num, "
."node.debit, "
."node.credit, "
."node.balance, "
."(COUNT(parent.name) - 1) AS level, "
."node.lft, "
."node.rgt "
."FROM accounts AS node, "
."accounts AS parent "
."WHERE node.lft BETWEEN parent.lft AND parent.rgt ".$ADDWHERE
." GROUP BY node.name "
." ORDER BY node.lft";
 
header("Content-type: text/html;charset=utf-8");
$response->page = 1;
$response->total = 1;
$response->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false';
    if( $n_lvl ==  $row[level]) { // we output only the needed level
        $response->rows[$i]['cell']=array($row[account_id],
            $row[name],
            $row[acc_num],
            $row[debit],
            $row[credit],
            $row[balance],
            $row[note],
            $row[level],
            $row[lft],
            $row[rgt],
            $leaf,
            'false'
        );
    }
    $i++;
} 
echo json_encode($response);
?>