Nested Set Model

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.

Field	Type	Description
level_field	number	this field determines the level in the hierarchy of the element. Usually the root element will be at level 0.The first child of the root is at level 1 and so on. This information is needed for the grid to set the ident of every element.
left_field	number	rowid of the field to the left
right_field	number	rowid of the field to the right
leaf_field	boolean	This field should tell the grid that the element is leaf. Possible values can be true and false. To the leaf element is attached diffrent image and this element can not be expanded or collapsed.
expanded_field	boolean	Tells the grid whether this element should be expanded during the loading (true or false). If the element has no value, false is set. Note that the data can be empty for this element, but this element can not be removed from data set.

The minimum information required to make the nested set model work is rowid, left_field, and right_field

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

Example

Data preparation

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.

Grid preparation

Our minimum configuration can look like this.

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",
      imgpath: 'images',
   caption: "Treegrid example"
});

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

Server side preparation: Loading all at once

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.

Using PHP/MySQL

// 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());

Using XML

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");
}
$et = ">";
$s = "";
$s .=  "<?xml version='1.0' encoding='utf-8'?$et\n";
$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;

Using Json

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);

Server side preparation: Auto loading tree

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).

Using Json

$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);