Subgrids

There are projects where 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.

Guriddo jqGrid JS offers two ways of handling child records:

• subGrid
• Grid as a subGrid

subGrid

When created this type of subGrid is a simple flat table. The table can not be sorted, paged or re-sized - it just display the information in table format. The data can not be edited and selected. Formatting is possible only with PHP code at server.

subGrids use properties, events and methods of the parent grid - i.e. they should be set as properties in the grid options.

Properties

use $grid->setGridOptions to set these parameters.

Property	Type	Description	Default
subGrid	boolean	If set to true this enables using a subGrid. If the subGrid is enabled additional column at left side is added to the basic grid. This column contains a 'plus' icon, which indicate that the user can click on it to expand the row. By default all rows are collapsed.	false
subGridOptions	array	A set of options for the subGrid. Below are all the options with their default values: js{ plusicon : classes.icon_plus, minusicon : classes.icon_minus, openicon: "ui-icon-carat-1-sw", expandOnLoad: false, selectOnExpand : false, selectOnCollapse:false, reloadOnExpand : true } plusicon and minusicon defies the icons when the grid is collapsed/expanded. The icons are defined in the styleUI object for the particular CSS - see subGrid property. openicon the icon bellow the minusicon when the subGrid row is expanded - see the same property from subGrid property. 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. selectOnCollapse when set to true the row is selected when a minusicon 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.
subGridModel	array	This 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 alignment 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 when click on the plus icon. 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.
subgridtype	mixed	This option allow loading subGrid as a service. If not set, the datatype parameter of the parent grid is used. See the example below	null
subGridWidth	integer	Determines the width of the subGrid column if subGrid is enabled.	20
subGridUrl	mixed	This 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 subGridModel. The option can be set as function. The parameter pass to this fumction is the id and the params data as defined in subGridModel.	empty
ajaxSubgridOptions	array	This 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
serializeSubGridData	function	If set this event can serialize the data passed to the subGrid 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. The event actually is used in subGrid ajax data parameter	null

Events

use $grid->setGridEvent to set these parameters.

The most of the following events use the parameters defined here:

• 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

Below is the list of "option" and triggered events. The name of every triggered event begin with jqGrid and is set below the "option" event.

Note

The first parameter of the trigered event is always event. It should be used before any other parameter. By example if the described parameters in options event are a,b,c - i.e event(a,b,c), in triggered event we need to use triggered_event(e, a, b, c)

Note

the triggered event is bind to the grid and is not passed to to any options. See Triggered events

---

subGridBeforeExpand( pID, id)
jqGridSubGridBeforeExpand(event pID, id)

The 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.

---

subGridRowExpanded( pID, id)
jqGridSubGridRowExpanded(event pID, id)

This 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 omitting the build-in ajax request.

---

subGridRowColapsed( pID, id)
jqGridSubGridRowExpanded(event pID, id)

This 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.

---

serializeSubGridData( sPostData )
no triggered event

If 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.

---

isHasSubGrid( row_id )
no triggered event

To the event is passed the row_id parameter. If defined it is executed on every row before the subgrid is build. If the event return false the subgrid for that row is not build-ed. The plus icon disappear.

Methods

use $grid->callGridMethod to use these methods

The following methods are available for manipulating SubGrid data.

---

expandSubGridRow( row_id )

Dynamically expand the subgrid row with the id = row_id

parameters

• string row_id the id of the row to be expended

return
jqGrid object

---

collapseSubGridRow( row_id )

Dynamically collapse the subgrid row with the id = row_id

parameters

• string row_id the id of the row to be collapsed

return
jqGrid object

---

toggleSubGridRow( row_id )

Dynamically toggle the subgrid row with the id = row_id

parameters

• string row_id - the id of the row

return
jqGrid object

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 :

PHP

$grid->callGridMethod("grid", "hideCol", array("subgrid"));

JavaScript

jQuery("#grid").jqGrid('hideCol', 'subgrid');

To enable subGrid

$grid->callGridMethod("grid", "showCol", array("subgrid"));

JavaScript

jQuery("#grid").jqGrid('showCol', 'subgrid');

Where

• grid is to be replaced by the id 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.

Dev information

All the code related to subGrid is in file grid.subgrid.js

When the subGrid is defined (subGrid: true in grid options) on additional column with name 'subgrid' is created and is placed at first place in the colModel. This column is not resizable, not sortable, can not be searched, it is fixed and can be hidden and visible using the appropriate methods and columnChooser.

The subGrid can not be used when the following options are set.

• when a treeGrid is set (treeGrid: true). In this case treeGrid internally disables the subGrid.
• when frozen columns are activated - in this case the frozen column method setFrozenColums does nothing.

When the user click on plus icon we create a new row below. This row has a class ui-subgrid. When the row is expanded a class ui-sg-expanded is added and when is collapsed a class ui-sg-collapsed is added, removing the previous expanded class.

The cell which contain plus/minus icon has a class subgrid-cell and the cell which contain the subgrid data has a class subgrid-data. To the subGrid data cell we add a attribute colspan equal to the number of visible columns.

The row which contain subGrid information has a id = gird id plus "" plus row id plus "" plus a word expandedContent. Example. If the grid has a id = jqGrid and the row has id = ALFKI, the the subGrid row id is: jqGrid_ALFKI_expandedContent

The PHP way

In order to make the development easy we have created a method named setSubGrid which can easy build simple subgrid.

The method has the following parameters:

    /**
     *
     * Prepares a execution of a simple subgrid
     * Return false if no name options for the subgrid.
     * @param string $suburl Url from where to get the data
     * @param array<int,mixed>|bool $subnames - the names that should correspond to fields of the data
     * @param array<int,mixed>|bool $subwidth (optional) - sets a width of the subgrid columns. Default 100
     * @param array<int,mixed>|bool $subalign (optional) - set the aligmend of the columns. default center
     * @param array<string,mixed>|bool $subparams (optional) additional parameters that can be passed when the subgrid plus icon is clicked. The names should be present in colModel in order to pass the values
     * @param array<int,mixed>|bool $mapping (optional) - map the names  from 
     * subGrid to these in case repeatitems in subGrid is set to false.
     * @return boolean
     */
    public function setSubGrid ($suburl='', $subnames=false, $subwidth=false, $subalign=false,  $subparams=false, $mapping=false)
    {
         ...
  }

The minimal set of parameters for this method are the $suburl and the $subnames. The first parameter point to the file from where to get the data and the second parameter describes the columns for the subgrid.

When the user click on the plus icon to open the subgrid, we automatically send the rowId parameter of the row clicked. The name of this parameter is called id. If there is a need to send additional parameters to the script use $subparams array where the name in it should correspond to the name from colModel

The jqGrid PHP class has one property and one method related to the simple subgrid - the property SubgridCommand and the method querySubgrid. Below we will show a working example demonstrating the use of simple subgrid.

It is possible to format the otput within PHP code using the customSubgrid property which is similar to the customFunc one described in jqGrid calss.

Let suppose that we have a table which list the customer information. Again with this we want to show quick all the orders related to certain customer. For this purpose we will use simple Subgrid.

The main table has the following code:

<?php
require_once '../../../../vendor/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");

// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn);
// Write the SQL Query
$grid->SelectCommand = 'SELECT CustomerID, CompanyName, ContactName, Phone, City FROM customers';
// Set the table to where you update the data
$grid->table = 'customers';
// Set output format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setPrimaryKeyId('CustomerID');
$grid->setColModel();
// Set the url from where we obtain the data
$grid->setUrl(Utils::fileUrl(__FILE__));
// Set some grid options
$grid->setGridOptions(array(
    "rowNum"=>10,
    "height"=>250,
    "gridview"=>false,
    "rowList"=>array(10,20,30),
    "sortname"=>"CustomerID"
));
$grid->setColProperty('CustomerID', array("label"=>"ID", "width"=>50));
// Set the parameters for the subgrid
$grid->setSubGrid(Utils::fileUrl("subgrid.php"),
        array('OrderID', 'RequiredDate', 'ShipName', 'ShipCity', 'Freight'),
        array(60,120,150,100,70),
        array('left','left','left','left','right'));
// Enable navigator
$grid->navigator = true;
// Enable only editing
$grid->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"del"=>false,"view"=>false));
// Enjoy
$grid->renderGrid('#grid','#pager',true, null, null, true,true);

The subgrid.php file definition is as follow:

<?php
require_once '../../../../vendor/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Get the needed parameters passed from the main grid
$rowid = Utils::GetParam("id");
if(!$rowid) {
    die("Missed parameters");
}
// Create the base jqGrid instance
$grid = new Guriddo\jqGrid\jqGrid($conn);
// Write the SQL Query
$grid->SubgridCommand = "SELECT OrderID, RequiredDate, ShipName, ShipCity, Freight FROM orders WHERE CustomerID=? ORDER BY OrderID desc";
// set the ouput format to json
$grid->dataType = 'json';
// Use the build in function for the simple subgrid
$grid->querySubGrid(array($rowid));

The querySubGrid method accept two parameters - the first one defines the parameters passed to the SubgridCommand (if any) and the second parameter defined if the output should be echoed (default)

subGrid as Grid

This is not a ready for you method, but rather a solution using some of the available methods and events. In this alternative to a subGrid, we use the subGrid functions of the main grid to create not a subGrid, but another grid, with all of the power and capacity of the main grid but appearing, as before, under the "parent" record with the same ability to reveal and hide it.

Definition

We use two events described in options array: subGridRowExpanded and subGridRowColapsed [note the unconventional spelling].

When these events are defined the population of the data in the subgrid is not executed. This way we can use the subGridUrl to get our custom data and put it into the expanded row.

For this purpose we have create a method which enables the subgrid and point to the data that will be inserted below the clicked row. Please note the term data - this way irt is possible to insert any content below the row and not only subgrids.

To enable this type of subGrid use the following method:

/**
 *
 * Prepares a subgrid in the grid expecting any valid html content provided
 * via the $suggridurl
 * @param string $subgridurl url from where to get html content
 * @param array<string> $subgridnames a array with names from colModel which
 * values will be posted to the server
 * @return bool 
 */
public function setSubGridGrid($subgridurl, $subgridnames=null) {...}

The method enables the subgrid in grid and via subgridurl using ajax load html content below the row. We can pas additional parameters to the subgridurl using the $subgridnames parameter which is array and the name in the array should correspond to the names in colModel.

Below we will provide similar example as of simple subgrid but using subgrid as grid.

The main grid.php diffres only by calling the method setSubGridGrid

....
// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn);
// Write the SQL Query
$grid->SelectCommand = 'SELECT CustomerID, CompanyName, ContactName, Phone, City FROM customers';
// Set the table to where you update the data
$grid->table = 'customers';
// Set output format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setColModel();
// Set the url from where we obtain the data
$grid->setUrl(Utils::fileUrl(__FILE__));
// Set some grid options
$grid->setGridOptions(array(
    "rowNum"=>10,
    "rowList"=>array(10,20,30),
    "sortname"=>"CustomerID"
));
$grid->setSubGridGrid(Utils::fileUrl("subgrid.php"));
...
$grid->renderGrid('#grid','#pager',true, null, null, true,true);

The subgrid.php file is a file which creates another grid, but with some additional checking and settings:

<?php
require_once '../../../../vendor/autoload.php';

use Guriddo\Utils\Utils;

// Connection to the server
$conn = new PDO(DB_DSN,DB_USER,DB_PASSWORD);
// Tell the db that we use utf-8
$conn->query("SET NAMES utf8");
// Get the needed parameters passed from the main grid
// By default we add to postData subgrid and rowid parameters in the main grid
$subtable = Utils::Strip($_REQUEST["subgrid"]);
$rowid = Utils::Strip($_REQUEST["rowid"]);

if(!$subtable && !$rowid) {
    die("Missed parameters");
}
// Create the jqGrid instance
$grid = new Guriddo\jqGrid\Render\Render($conn);
// Write the SQL Query
$grid->SelectCommand = "SELECT OrderID, RequiredDate, ShipName, ShipCity, Freight FROM orders WHERE CustomerID = ?";
// set the ouput format to json
$grid->dataType = 'json';
// Let the grid create the model
$grid->setColModel(null,array(&$rowid));
// Set the url from where we obtain the data
$grid->setUrl(Utils::fileUrl('subgrid.php'));
// Set some grid options
$grid->setGridOptions(array(
    "autowidth"=>true,
    "rowNum"=>10,
    "sortname"=>"OrderID",
    "height"=>110,
    "postData"=>array("subgrid"=>$subtable,"rowid"=>$rowid)));
// Change some property of the field(s)
$grid->setColProperty("RequiredDate", array(
    "formatter"=>"date",
    "formatoptions"=>array("srcformat"=>"Y-m-d H:i:s","newformat"=>"m/d/Y"),
    "search"=>false
    )
);
$grid->navigator = true;
$grid->setNavOptions('navigator', array("excel"=>false,"add"=>false,"edit"=>false,"del"=>false,"view"=>false));
// Enjoy
$subtable = $subtable."_t";
$pager = $subtable."_p";
$grid->renderGrid($subtable,$pager, true, null, array(&$rowid), true,true);

As can be seen we accept two parameters when the user click to expand the row - the id of the row and the subgridtable which is needed to build unique grid id when we create it.

The SelectCommand is configured to accept the id and the renderGrid to accept the unique names for the grid id and the pager id.