Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
wiki:adjacency_model [2009/07/28 13:30]
tony
wiki:adjacency_model [2017/12/12 17:21]
admin
Line 1: Line 1:
 +====== Adjacency 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 adjacency model is: 
 +<code javascript>​
 +treeReader = {
 +   ​level_field:​ "​level",​
 +   ​parent_id_field:​ "​parent",​ // then why does your table use "​parent_id"?​
 +   ​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.
 +
 +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.
 +
 +^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.|
 +|parent_id_field|mixed|indicates 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_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.|
 +
 +===== 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,​
 +   ​parentid:​rc.parent_id,​
 +   ​n_level:​rc.level ​  
 +   ...
 +}
 +</​code>​
 +
 +  * 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
 +
 +<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',​
 +  parent_id int(11) default 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, 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);
 +</​code>​
 +With this information we can now construct the treeGrid.
 +==== Grid configuration ====
 +
 +<code javascript>​
 +<​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>​
 +</​code>​
 +
 +==== 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 ===
 +<code php>
 +<?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>";​
 +?>
 +</​code>​
 +
 +==== 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 ===
 +<code php>
 +<?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>";​
 +?>
 +</​code>​
  

QR Code
QR Code wiki:adjacency_model (generated for current page)