====== Nested Set Model ======

As discussed in previous chapter [[:wiki:treegrid | 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. 

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

<code javascript>
treeReader : {
    level_field: "level",
    left_field:"lft",
    right_field: "rgt",
    leaf_field: "isLeaf",
    expanded_field: "expanded"
}
</code>

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

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.

===== What we post? =====
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 [[wiki:treegrid#cautions_and_limitations | here]]
<code javascript>
postData : {
   ...
   nodeid:rc.id,
   n_left:rc.lft,
   n_right:rc.rgt,
   n_level:rc.level,
   ...
}
</code>

  * 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

===== Example =====
In order to understand the the process of configuring the tree grid here we provide full 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

<code sql>
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`)
);
</code>

Let's add some data:
<code sql>
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);
</code>
With this information we can now construct the treeGrid.
==== Grid preparation ====
<code javascript>
<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>
</code>
Since jqGrid currently does not support paging, when we have a treegrid the pager elements are disabled automatically.

==== Server Code: Loading 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

<code 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;
</code>

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.
<code php>
<?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());
</code>

=== Using XML ===

<code php>
<?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;
?>
</code>
=== Using Json ===
<code php>
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);
?>
</code>

<note tip>If you want that all the nodes to be expanded when the data is loaded set the last value to true</note>

==== Server Code: 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 ===

<code php>
<?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);
?>
</code>