Adjacency Model

To use the Adjacency model, set in grid options

treeModel : 'adjacency'

The default treeReader array for this model is

treeReader = {
   level_field: "level",
   parent_id_field: "parent",
   leaf_field: "isLeaf",
   expanded_field: "expanded"
}

The only diffrence 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.

For explanantion of the other fields, see Nested Set model

Example

Data preparation

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.

Grid configuration

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

Server side preparation: Loading all the nodes at once

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 (desscribed below) is much simpler and does not require recursion.

Using XML

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

Server side preparation: Auto loading tree

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.

Using XML

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