jqGrid:treeGrid嵌套集合模型

  如jqGrid treegrid配置中提到的,treeReader属性很重要。了解分层数据管理-嵌套集合模式的基本概念,将会更容易配置这个属性。

treeReader配置

  默认嵌套集合模型的(nested set model)treeReader配置如下

treeReader : {
    level_field: "level",
    left_field:"lft",
    right_field: "rgt",
    leaf_field: "isLeaf",
    expanded_field: "expanded"
}

	

  treeReader会自动在colModel其他项后面添加这些隐藏字段。从服务器返回的数据需要一起包含这些配置的数据,以便构造树形表格。可以扩展treeReader其他字段用于满足你的需求。左右值详细介绍参考:分层数据管理-嵌套集合

字段名称 类型 描述
level_field number 定义元素所在层级(this field determines the level in the hierarchy of the element.)。根元素level为0。根节点的子元素level为1,依次类推。用于设置元素的缩进
left_field number 此数据行对应的左值
right_field number 此数据行对应的右值
leaf_field boolean 是否为叶子节点,true/false。指定为叶子节点会有不同的图标,不能被展开或收缩
expanded_field boolean (true/false)树加载时指定非叶子节点是否展开。默认为false。注意:这个配置允许为空值,但是一定要包含在数据中,不能删除这个配置项。

  另外一个可修改选项为tree_root_level,默认值为0,配置哪层数据作为根元素。

向服务器提交什么数据

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

postData : {
   ...
   nodeid:rc.id,
   n_left:rc.lft,
   n_right:rc.rgt,
   n_level:rc.level,
   ...
}

	
  • nodeid :当前展开的数据行id
  • n_left :当前展开行的左值
  • n_right:当前展开行的右值
  • n_level :当前展开行的level值(contain the level value of the currently expanded row)

示例

数据准备

假设有一个账号表,一些子账号属于主账号,而一些账号没有子账户。在嵌套集合模式下表格字段如下

account_id, name, account_number, Debit, Credit, Balance, lft, rgt

  • account_id :账户的唯一id(在grid中为对应rowid)
  • lft :表示 left_field
  • rgt:表示 right_field

在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',
  lft int(11) NOT NULL,
  rgt int(11) NOT NULL,
  PRIMARY KEY  (`account_id`)
);

添加一些数据

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

下面介绍如何构造treeGrid

表格配置

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

  启用treeGrid将会自动禁用分页元素,不支持分页操作。

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

  如果数据表格中的关系不复杂可以一次加载所有节点数据,SQL语句如下

SELECT
    node.account_id,
    node.name,
    node.acc_num,
    node.debit,
    node.credit,
    node.balance,
    (COUNT(parent.name) - 1) AS level,
    node.lft,
    node.rgt
FROM accounts AS node,
accounts AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft;

	

  在嵌套集合模型,判断一个节点是否为叶子节点很简单,只需要比较 rgt = lft+1。

  服务器端代码,下面为php+mysql的示例,返回xml或者json

// this query determines the total number of records in the tree (can be omitted)
$result = mysql_query("SELECT COUNT(*) as count FROM accounts");
$row = mysql_fetch_array($result,MYSQL_ASSOC);
$count = $row['count'];
// the actual query
$SQL = "SELECT "
."node.account_id, "
."node.name, "
."node.acc_num, "
."node.debit, "
."node.credit, "
."node.balance, "
."(COUNT(parent.name) - 1) AS level, "
."node.lft, "
."node.rgt "
."FROM accounts AS node, "
."accounts AS parent "
."WHERE node.lft BETWEEN parent.lft AND parent.rgt "
."GROUP BY node.name "
."ORDER BY node.lft";
 
$result = mysql_query( $SQL ) or die("Couldn’t execute query.".mysql_error());

XML

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");
}
 
$s = "<?xml version='1.0' encoding='utf-8'?>";
$s .=  "<rows>";
$s .=  "<page>1</page>";
$s .=  "<total>1</total>";
$s .=  "<records>".$count."</records>";
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
   $s .=  "<row>";         
   $s .=  "<cell>". $row[account_id]."</cell>";  // the id of the row is setted in colmodel, no need to put id in row
   $s .=  "<cell>". $row[name]."</cell>";
   $s .=  "<cell>". $row[acc_num]."</cell>";
   $s .=  "<cell>". $row[debit]."</cell>";
   $s .=  "<cell>". $row[credit]."</cell>";
   $s .=  "<cell>". $row[balance]."</cell>";
   $s .=  "<cell>". $row[level]."</cell>"; // level element
   $s .=  "<cell>". $row[lft]."</cell>"; // left_field element
   $s .=  "<cell>". $row[rgt]."</cell>"; // right_field element
   if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false'; // this determines if the node is aleaf
   $s .=  "<cell>".$leaf."</cell>"; // isLief element
   $s .=  "<cell>false</cell>"; // expanded element - we set by default t false
   $s .=  "</row>";
}
$s .=  "</rows>";      
 
echo $s;

Json

header("Content-type: text/html;charset=utf-8");
$response->page = 1;
$response->total = 1;
$response->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false';
    $response->rows[$i]['cell']=array($row[account_id],
        $row[name],
        $row[acc_num],
        $row[debit],
        $row[credit],
        $row[balance],
        $row[note],
        $row[level],
        $row[lft],
        $row[rgt],
        $leaf,
        'false'
    );
    $i++;
} 
echo json_encode($response);

如果你需要展开所有节点,设置最后一个值为true。

服务器端代码:动态加载

  如果大量关联数据,最好在我们需要的时候再加载它,就是当点击父节点时才会加载子节点数据。初始化时只显示根元素,点击根元素时,grid自动检查是否存在子节点数据,没有就传递需要的参数到服务器加载需要信息,所以level_field 和 isLeaf field 非常重要。

  这样使用之前的查询获取当前层需要的数据即可。(查询还可以优化,但是超出本文范围)

Json

$ADDWHERE = '';
$node = (integer)$_REQUEST["nodeid"];
// detect if here we post the data from allready loaded tree
// we can make here other checks
if( $node >0) {
   $n_lft = (integer)$_REQUEST["n_left"];
   $n_rgt = (integer)$_REQUEST["n_right"];
   $n_lvl = (integer)$_REQUEST["n_level"];			
   $ADDWHERE = " AND lft > ".$n_lft." AND rgt < ".$n_rgt;
} else { 
   // initial grid
   $n_lvl =0;
}
$SQL1 = "SELECT "
."node.account_id, "
."node.name, "
."node.acc_num, "
."node.debit, "
."node.credit, "
."node.balance, "
."(COUNT(parent.name) - 1) AS level, "
."node.lft, "
."node.rgt "
."FROM accounts AS node, "
."accounts AS parent "
."WHERE node.lft BETWEEN parent.lft AND parent.rgt ".$ADDWHERE
." GROUP BY node.name "
." ORDER BY node.lft";
 
header("Content-type: text/html;charset=utf-8");
$response->page = 1;
$response->total = 1;
$response->records = $count;
$i=0;
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) {
    if($row[rgt] == $row[lft]+1) $leaf = 'true';else $leaf='false';
    if( $n_lvl ==  $row[level]) { // we output only the needed level
        $response->rows[$i]['cell']=array($row[account_id],
            $row[name],
            $row[acc_num],
            $row[debit],
            $row[credit],
            $row[balance],
            $row[note],
            $row[level],
            $row[lft],
            $row[rgt],
            $leaf,
            'false'
        );
    }
    $i++;
} 
echo json_encode($response);

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

 


原创文章,转载请注明出处:jqGrid:treeGrid嵌套集合模型

评论(0)Web开发网
阅读(2343)喜欢(0)不喜欢(0)jqGrid中文API