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.

treeReader Configuration

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.

FieldTypeDescription
level_fieldnumberthis 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.
parent_id_fieldmixedindicates if the record has a parent with an id of parent_id_field. If the parent id is NULL the element is a root element
leaf_fieldbooleanThis 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_fieldbooleanTells 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.

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

Example

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

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

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

Using XML

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

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

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

Discussion

dutch201, 2011/10/28 14:32

Adjacency Model page shows mainly nested model code and vice versa for the nested model page.

peter, 2012/06/17 16:10

Hello!!! I have try this example and there is a lot of bug and not working. Have you try it before post this example?

Michele Inglis, 2012/11/05 18:11

I am looking at this example and would like to know if there are any examples that fit an asp.net mvc app in c#. I have a working version in adjacency mode but the amount of documents just jumped to the point where the time to load is just tooooo long!


Personal Tools
;