Adjacency 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 adjacency model is:

treeReader = {
   level_field: "level",
   parent_id_field: "parent", // then why does your table use "parent_id"?
   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.

The only difference from nested set model is that the left_field and right_field are replaced with parent_id_field. This element indicates that the record has a parent with an id of parent_id_field. If the parent id is NULL the element is a 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,
   parentid:rc.parent_id,
   n_level:rc.level   
   ...
}

• nodeid contain the id of the currently expanded record
• parentid is the parent_id 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 suppose that we have account table where some accounts are children of the main accounts and some accounts have no child account. In the Adjacency model the table can look like this
account_id, name, account_number, Debit, Credit, Balance, parent_id
where:

• account_id is the uniquie id of the account (in our grid this should be the rowid)
• parent_id indicates the parent_id_field in the grid

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',
  parent_id INT(11) DEFAULT NULL,
  PRIMARY KEY  (`account_id`)
);

Let's add some data

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

With this information we can now construct the treeGrid.

<script>
...
jQuery("#treegrid").jqGrid({
    treeGrid: true,
    treeGridModel: 'adjacency',
    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>

Loading all the nodes at once works well when we have relatively few elements and the tree has only a few levels.
Loading data in the Adjacency model is little difficult, since it requires recursion and, where the depth of the tree is great, this will take a lot of time. There are some techniques that overcome this problem, but in our case we will use the standard approach. Autoloading tree nodes (described below) is much simpler and does not require recursion.

<?php
// First we need to determine the leaf nodes
$SQLL = "SELECT t1.account_id FROM accounts AS t1 LEFT JOIN accounts as t2 "
." ON t1.account_id = t2.parent_id WHERE t2.account_id IS NULL";
$result = mysql_query( $SQLL ) or die("Couldn t execute query.".mysql_error());
$leafnodes = array();
while($rw = mysql_fetch_array($result,MYSQL_ASSOC)) {
   $leafnodes[$rw[account_id]] = $rw[account_id];
}
 
// Recursive function that do the job
function display_node($parent, $level) {
   global $leafnodes;
   if($parent >0) {
      $wh = 'parent_id='.$parent;
   } else {
      $wh = 'ISNULL(parent_id)';
   }
   $SQL = "SELECT account_id, name, acc_num, debit, credit, balance, parent_id FROM accounts WHERE ".$wh;
   $result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());
   while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
      echo "<row>";         
      echo "<cell>". $row[account_id]."</cell>";
      echo "<cell>". $row[name]."</cell>";
      echo "<cell>". $row[acc_num]."</cell>";
      echo "<cell>". $row[debit]."</cell>";
      echo "<cell>". $row[credit]."</cell>";
      echo "<cell>". $row[balance]."</cell>";
      echo "<cell>". $level."</cell>";
      if(!$row[parent_id]) $valp = 'NULL'; else $valp = $row[parent_id];  // parent field
      echo "<cell><![CDATA[".$valp."]]></cell>";
      if($row[account_id] == $leafnodes[$row[account_id]]) $leaf='true'; else $leaf = 'false';  // isLeaf comparation
      echo "<cell>".$leaf."</cell>"; // isLeaf field
      echo "<cell>false</cell>"; // expanded field
      echo "</row>";
        // recursion
      display_node((integer)$row[account_id],$level+1);
   }
}
 
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 = ">";
echo "<?xml version='1.0' encoding='utf-8'?$et\n";
echo "<rows>";
echo "<page>1</page>";
echo "<total>1</total>";
echo "<records>1</records>";
// Here we call the function at root level
display_node('',0);
echo "</rows>";
?>

Auto loading the tree is the recommeded approach when using adjacency model in jqGrid. Here, we can make simple query without any === need to provide for recursion.

<?php
// We need first to determine the leaf nodes
$SQLL = "SELECT t1.account_id FROM accounts AS t1 LEFT JOIN accounts as t2 "
   ." ON t1.account_id = t2.parent_id WHERE t2.account_id IS NULL";
$resultl = mysql_query( $SQLL ) or die("Couldn t execute query.".mysql_error());
$leafnodes = array();
while($rw = mysql_fetch_array($resultl,MYSQL_ASSOC)) {
   $leafnodes[$rw[account_id]] = $rw[account_id];
}
 
// Get parameters from the grid
$node = (integer)$_REQUEST["nodeid"];
$n_lvl = (integer)$_REQUEST["n_level"];
 
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 = ">";
echo "<?xml version='1.0' encoding='utf-8'?$et\n";
echo "<rows>";
echo "<page>1</page>";
echo "<total>1</total>";
echo "<records>1</records>";
 
if($node >0) { check to see which node to load
   $wh = 'parent_id='.$node; // parents
   $n_lvl = $n_lvl+1; // we should ouput next level
} else {
   $wh = 'ISNULL(parent_id)'; // roots
}
 
$SQL = "SELECT account_id, name, acc_num, debit, credit, balance, parent_id FROM accounts WHERE ".$wh;
 
$result = mysql_query( $SQL ) or die("Couldn t execute query.".mysql_error());
 
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
   echo "<row>";         
   echo "<cell>". $row[account_id]."</cell>";
   echo "<cell>". $row[name]."</cell>";
   echo "<cell>". $row[acc_num]."</cell>";
   echo "<cell>". $row[debit]."</cell>";
   echo "<cell>". $row[credit]."</cell>";
   echo "<cell>". $row[balance]."</cell>";
   echo "<cell>". $n_lvl."</cell>";
   if(!$row[parent_id]) $valp = 'NULL'; else $valp = $row[parent_id]; 
   echo "<cell><![CDATA[".$valp."]]></cell>";
   if($row[account_id] == $leafnodes[$row[account_id]]) $leaf='true'; else $leaf = 'false';
   echo "<cell>".$leaf."</cell>";
   echo "<cell>false</cell>";
   echo "</row>";
}
echo "</rows>";
?>