jqGrid：treeGrid嵌套集合模型

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

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

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

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;

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

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