Subgrid

There are times when we need to be able to easily display (or edit) records that are the children of a selected record in the parent grid. We would, of course, want to show only those records that are the children of the selected record in the grid, never the children of all records.

jqGrid offers two ways of handling child records:

  • a subGrid
  • a grid as a subGrid

Installation

In order to use this module you should mark the Subgrid when you download the grid. For more information refer to Download.
For Developers - this is the grid.subgrid.js in the src directory.

Properties

SubGrids use the following properties, events and methods of the parent grid - i.e. you should set these properties in the grid options

PropertyTypeDescriptionDefault
subGridbooleanIf set to true this enables using a subgrid. If the subGrid is enabled a additional column at left side is added to the basic grid. This column contains a 'plus' image which indicate that the user can click on it to expand the row. By default all rows are collapsed.false
subGridOptions object A set of options for the subgrid. Below are all the options with their default values
{
plusicon : “ui-icon-plus”,
minusicon : “ui-icon-minus”,
openicon: “ui-icon-carat-1-sw”,
expandOnLoad: false,
selectOnExpand : false,
reloadOnExpand : true
}
plusicon and minusicon defies the icons when the grid is collapsed/expanded. A valid name of icon from Theme Roller should be set.
openicon the icon bellow the minusicon when the subgrid row is expanded
expandOnLoad when set to true make it so that all rows will be expanded automatically when a new set of data is loaded.
selectOnLoad when set to true the row is selected when a plusicon is clicked with the mouse.
reloadOnExpand If set to false the data in the subgrid is loaded only once and all other subsequent clicks just hide or show the data and no more ajax calls are made.
subGridModelarrayThis property, which describes the model of the subgrid, has an effect only if the subGrid property is set to true. It is an array in which we describe the column model for the subgrid data. The syntax is :
subGridModel : [
{ name : ['name_1','name_2',…,'name_n'],
width : [width_1,width_2,…,width_n] ,
align : ['left','center',…,'right'] ,
params : [param_1,…,param_n],
mapping:['name_1_map','name_2_map',…,'name_n_map']}
Where
name: an array containing the labels of the columns of the subgrid.
width: an array containing the width of the columns. This array should have the same length as in name array.
align: an array containing the alignment of the columns. The values can be left, center or right. If omited the aliment is left.
params: an array in which we can add a name from main grid's colModel to pass as additional parameter to the subGridUrl.
mapping: the parameter is used only when repeatitems in subgrid is set to false. When defined in this case we use the names from this array to map it to the subgrid names. If not set and repeatitems is false we use the name option.
subgridtypemixedThis option allow loading subgrid as a service. If not set, the datatype parameter of the parent grid is used. See the example belownull
subGridWidthintegerDetermines the width of the subGrid column if subgrid is enabled. 20
subGridUrlstringThis option has effect only if subGrid option is set to true. This option points to the file from which we get the data for the subgrid. jqGrid adds the id of the row to this url as parameter. If there is a need to pass additional parameters, use the params option in subGridModelempty string
ajaxSubgridOptionsobjectThis option allow to set global ajax settings for the subgrid when we request data. Note that with this option is possible to overwrite all current ajax setting in the subgrid request including the complete event.empty object

In order to configure the subgrid you will need to configure the subgrid item in xmlReader or jsonReader. The default setting for these are:

xmlReader : {
...
   subgrid: {
      root: "rows", 
      row: "row", 
      repeatitems: true, 
      cell: "cell"
   }
}

and a jsonReader

jsonReader : {
...
   subgrid: {
      root: "rows", 
      repeatitems: true, 
      cell: "cell"
   }
}

The mapping rules are the same as those in the basic grid. For more information refer to Retrieving Data.

In order to use correct subGridType as service below is a simple code which shows how this can be achieved:

jQuery("#grid_id").jqGrid({
...
   subgridtype: function(rowidprm) {
      jQuery.ajax({
         url:'url_to_the_service',
         data:rowidprm,
         dataType:"json",
         complete: function(jsondata,stat){
            if(stat=="success") {
               var thegrid = jQuery("#grid_id")[0];
               thegrid.subGridJson(eval("("+jsondata.responseText+")"),rowidprm.id);
            }
         }
      });
   },subgrid
...
});

Where rowidprm is array that contains the id of the row plus other parameters as required to set subGridModel parameters and subGridJson is a method which is described below.

the use of the variable thegrid

Events

In these events,

  • pID is the unique id of the div element where we can put contents when subgrid is enabled,
  • id is the id of the row
  • sPostData - the data which is posted when a subgrid request is made
EventParametersDescription
subGridBeforeExpandpID, idThe event is raised just before expanding the grid. When set, this event should return true or false. If it returns false the subgrid row is not expanded and the subgrid is not opened.
subGridRowExpandedpID, idThis event is raised when the subgrid is enabled and is executed when the user clicks on the plus icon of the grid. Can be used to put custom data in the subgrid.
subGridRowColapsedpID, idThis event is raised when the user clicks on the minus icon. The event should return true or false; when the returned value is false the row can not be collapsed.
serializeSubGridDatasPostDataIf set this event can serialize the data passed to the ajax request. The function should return the serialized data. This event can be used when a custom data should be passed to the server - e.g - JSON string, XML string and etc.

Methods

MethodParametersReturnsDescription
expandSubGridRowrowidjqGrid objectdynamically expand the subgrid row with the id = rowid
collapseSubGridRowrowidjqGrid objectdynamically collapse the subgrid row with the id = rowid
toggleSubGridRowrowidjqGrid objectdynamically toggle the subgrid row with the id = rowid
subGridJsonjson, rowidfalseAdd data in a subgrid row. json is a json object, rowid is the id of the row after which the data will be added
subGridXmlxml, rowidfalseAdd data in a subgrid row. xml is a xml dom element, rowid is the id of the row after which the data will be added

Example

Continuing to use the example from the tutorial, let's suppose that there is a need to display the line items for each invoice in a subgrid. The Java script code should look like this.

<script type="text/javascript">
jQuery(document).ready(function(){ 
  jQuery("#list").jqGrid({
    url:'example.php',
    datatype: 'xml',
    mtype: 'GET',
    colNames:['Inv No','Date', 'Amount','Tax','Total','Notes'],
    colModel :[ 
      {name:'invid', index:'invid', width:55}, 
      {name:'invdate', index:'invdate', width:90}, 
      {name:'amount', index:'amount', width:80, align:'right'}, 
      {name:'tax', index:'tax', width:80, align:'right'}, 
      {name:'total', index:'total', width:80, align:'right'}, 
      {name:'note', index:'note', width:150, sortable:false} 
    ],
    pager: '#pager',
    rowNum:10,
    rowList:[10,20,30],
    sortname: 'invid',
    sortorder: 'desc',
    viewrecords: true,
    caption: 'My first grid',
    subGrid: true,
    subGridUrl : "subgrid.php",
    subGridModel : [ 
      {
      name  : ['No', 'Item', 'Qty', 'Unit', 'Line Total'],
      width : [55, 200, 80, 80, 80],
      align : ['left','left','right','right','right'],
      params: ['invdate'] 
      }
    ]
  }); 
}); 
</script>

The next step is to configure the subgrid.php file. The example is in PHP and MySQL.
We suppose that the table name that contain the invoice item data has name invlines and has the following names: invid, item_num, item, qty, unit

subgrid
<?php
// get the id passed automatically to the request
$id = $_GET['id']; 
// get the invoice date passed to this request via params array in
//subGridModel. We do not use it here - this is only demostration
$date_inv = $_GET['invdate'];
 
		suboptions = {
			plusicon : "ui-icon-plus",
			minusicon : "ui-icon-minus",
			openicon: "ui-icon-carat-1-sw",
			expandOnLoad:  false,
			delayOnLoad : 50,
			selectOnExpand : false,
			reloadOnExpand : true
		};
 
// connect to the database 
$db = mysql_connect($dbhost, $dbuser, $dbpassword) or die("Connection Error: " . mysql_error()); 
 
mysql_select_db($database) or die("Error conecting to db."); 
 
// construct the query		suboptions = {
			plusicon : "ui-icon-plus",
			minusicon : "ui-icon-minus",
			openicon: "ui-icon-carat-1-sw",
			expandOnLoad:  false,
			delayOnLoad : 50,
			selectOnExpand : false,
			reloadOnExpand : true
		};
 
$SQL = "SELECT item_num, item, qty, unit FROM invlines WHERE invid=".$id." ORDER BY item"; 
$result = mysql_query( $SQL ) or die("Couldn?t execute query.".mysql_error()); 
 
// set the header information
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"); 
} 
 
echo "<?xml version='1.0' encoding='utf-8'?>"; 
echo "<rows>"; 
// be sure to put text data in CDATA 
while($row = mysql_fetch_array($result,MYSQL_ASSOC)) { 
            echo "<row>"; 
            echo "<cell>". $row[item_num]."</cell>"; 
            echo "<cell><![CDATA[". $row[item]."]]></cell>"; 
            echo "<cell>". $row[qty]."</cell>"; 
            echo "<cell>". $row[unit]."</cell>"; 
            echo "<cell>". number_format($row[qty]*$row[unit],2,'.',' ')."</cell>"; 
            echo "</row>"; 
} 
echo "</rows>";
 
?>

Enable/Disable Subgrid

A subGrid can be enabled (or disabled) dynamically (to respond to changes in the data in the main grid, for example).
To disable subgrid :

jQuery("#grid_id").hideCol('subgrid');

To enable subgrid

jQuery("#grid_id").showCol('subgrid');

Where

  • grid_id is to be replaced by the name of your grid, but
  • subgrid is a keyword, not to be replaced

To make this work, subGrid must be initially set to true in the jqGrid properties; only then can we enable and disable it using the code above.

Discussion

Juan Miguel Sosso, 2011/11/11 15:17

I have my subgrid data in the same XML as the main grid:

<invoice>
  <num>...</num>
  <date>...</date>
  <detail>
    <line>...</line>
    <line>...</line>
  </detail>
...
</invoice>

Is there any way to deal with this data structure? I think it will be faster than sending one request for every detail.

Ole Frank, 2013/03/14 11:11

I have the same problem…

Did you find a solution?

Norm Cousineau, 2012/06/06 19:25

Can subgrid be used to display custom html markup, rather than data?


Personal Tools
;
;