jqGrid：treeGrid邻接模型

　　如jqGrid treegrid配置中提到的，treeReader属性很重要。了解分层数据管理-邻接表模型模式的基本概念，将会更容易配置这个属性。

treeReader配置

　　默认邻接模型的（adjacency model）treeReader配置如下

treeReader = {
   level_field: "level",
   parent_id_field: "parent", // then why does your table use "parent_id"?
   leaf_field: "isLeaf",
   expanded_field: "expanded"
}

　　treeReader会自动在colModel其他项后面添加这些隐藏字段。从服务器返回的数据需要一起包含这些配置的数据，以便构造树形表格。可以扩展treeReader其他字段用于满足你的需求。

　　和嵌套集合模型唯一不同就是 left_field和 right_field被替换为parent_id_field。这个元素指定此记录集的父记录id。如果parent 为NULL ，那么这个元素为根元素

字段名	类型	描述
level_field	number	定义元素所在层级（this field determines the level in the hierarchy of the element.）。根元素level为0。根节点的子元素level为1，依次类推。用于设置元素的缩进
parent_id_field	mixed	这个元素指定此记录集的父记录id。如果parent 为NULL ，那么这个元素为根元素
leaf_field	boolean	是否为叶子节点，true/false。指定为叶子节点会有不同的图标，不能被展开或收缩
expanded_field	boolean	（true/false）树加载时指定非叶子节点是否展开。默认为false。注意：这个配置允许为空值，但是一定要包含在数据中，不能删除这个配置项。

向服务器提交什么数据

　　配置读取器（reader）后需要知道向服务器提交了什么，好知道如何加载子节点。自动加载树节点提交的参数如下，添加到postData中。

postData : {
   ...
   nodeid:rc.id,
   parentid:rc.parent_id,
   n_level:rc.level   
   ...
}

nodeid ：当前展开的数据行id
parentid：被展开行父行id
n_level ：当前展开行的level值

示例

数据准备

假设有一个账号表，一些子账号属于主账号，而一些账号没有子账户。在邻接模式下表格字段如下

account_id, name, account_number, Debit, Credit, Balance, parent_id

account_id ：账户的唯一id（在grid中为对应rowid）
parent_id：被展开行父行id

在mysql中创建表格的的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`)
);

添加一些数据

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

下面介绍如何构造treeGrid

表格配置

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"
});

服务器端代码：一次性加载

　　如果数据表格中的关系不复杂和树的层级不是很多可以一次加载所有节点数据。使用邻接模式加载数据比较麻烦，需要递归，当树的深度很大时，需要花费比较多时间。有很多技术可以解决这个问题，但是我们使用标准途径。自动加载树节点（如下所示）比较简单，不需要递归。

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

服务器端代码：动态加载

　　使用邻接模型时，动态加载为推荐的方法，使查询变得简单，而不需要很多个==来递归查询。

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

来源：http://www.trirand.com/jqgridwiki/doku.php?id=wiki:adjacency_model

加支付宝好友偷能量挖...

原创文章，转载请注明出处：jqGrid：treeGrid邻接模型

2013-7-9评论(0)Web开发网

阅读(2006)喜欢(0)分类：jqGrid中文API