Fork me on GitHub

ParamQuery Grid Tutorial

Introduction

ParamQuery is an initiative to create open source and web based counterpart to what Excel has to offer to PC environment. There are so many grids available on the web, so why another grid. These so called grids suffer from either poor performance or lack of extensibility. Their support for virtual rendering is only experimental and looks like a second citizen. ParamQuery is exactly to fill that void. Its core design is engineered using virtual rendering that's why it's unique and is a great performer. It maintains its grace & responsiveness irrespective of number of records. ParamQuery grid offers a rich API which gets one started and evolves with one's needs. It also exposes low level methods and events which makes it a great flexible and extensible tool.

PQ Grid chose jQueryUI widget design pattern as jQuery is the de facto language of choice for any serious javascript professional or enthusiast. If one has used any jQuery UI control, one would feel at home while using ParamQuery Grid.

A Word about its design

ParamQuery Grid keeps its data and view separate. It displays or renders only the rows which fit in its viewport. It refreshes its view whenever the following takes place:

  • View is scrolled either horizontally or vertically.
  • Any paging event.
  • Any sorting event.
  • Any resize event.
  • $( ".selector" ).pqGrid( "refresh" ) is invoked.
  • $( ".selector" ).pqGrid( "refreshDataAndView" ) is invoked.

ParamQuery Grid stores data for both local & remote requests in dataModel > data which is a 2 dimensional array or JSON (array of key/value paired objects). It refreshes its local copy of data for remote requests whenever the following takes place:

  • Any paging event.
  • Any sorting event.
  • $( ".selector" ).pqGrid( "refreshDataAndView" ) is invoked.

dataModel > data can be manipulated directly for both local and remote requests.
$( ".selector" ).pqGrid( "refreshDataAndView" ) is invoked usually after direct manipulation of dataModel properties to put the data and view in sync. $( ".selector" ).pqGrid( "refresh" ) is invoked usually after modification of the grid DOM.

ParamQuery Grid can sort the data locally for data loaded through remote request if all the data is loaded during initialization of the grid. It's helpful when sorting logic is not available on the remote server or while loading remote data from static file. In this case we pass the options { location:"remote", sorting:"local" } to pqGrid. e.g. Grid from XML File

ParamQuery Grid can also provide local paging functionality for data loaded through remote request if all the data is loaded during initialization of the grid. It's helpful when paging logic is not available on the remote server or while loading remote data from static file. In this case we pass the options { location:"remote", paging:"local" } to pqGrid. e.g. Grid from XML File

ParamQuery Grid includes 2 independent jQueryUI widgets/components : pqPager and pqScrollBar. pqPager has class pq-pager, hence full funtionality of pqpager can be accessed by $( ".pq-pager", $grid ).pqPager(). Similarly full funtionality of vertical and horizontal scrollbars can be accessed by $( ".pq-scrollbar-vert", $grid ).pqScrollBar() & $( ".pq-scrollbar-horiz", $grid ).pqScrollBar(). These default components can also be replaced with pagers and scrollbars having custom logic!

Include files

To use ParamQuery Grid, first of all include these files in the header section of your web page.

   <!--jQuery dependencies-->
    <link rel="stylesheet" 
        href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/themes/base/jquery-ui.css" />
    <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>    
    <script src="http://ajax.googleapis.com/ajax/libs/jqueryui/1.9.2/jquery-ui.min.js"></script>

    <!--Include Touch Punch file to provide support for touch devices-->
    <script type="text/javascript" src="path to touch-punch.js" ></script>   

    <!--ParamQuery Grid files-->
    <link rel="stylesheet" href="path to pqgrid.min.css" />
    <script type="text/javascript" src="path to pqgrid.min.js" ></script>   

Top 3 files point to jQuery and jQueryui css and javascript dependency files. They can point to CDN repositories (recommended) as above or they can point to the files kept on server locally.

Touch-punch.js file which can be obtained from https://github.com/furf/jquery-ui-touch-punch can be included if support for touch devices is desired. It works well for jQueryUI and ParamQuery Grid.

Bottom 2 files point to ParamQuery Grid files where path to files has to be replaced by absolute or relative URL.

If you want to add custom theme (e.g. office theme) then you would require to add another css file below it.

Getting Started

In the script tags and within the jQuery ready function, initialize the grid as $( ".selector" ).pqGrid( obj ); where obj is a PlainObject passed to the pqGrid constructor function.

obj provides information about width, height, columns information, data, location and type of data, etc.

colModel and dataModel are the 2 main keys of information in the obj.

As their names indicate, the main role of dataModel is to provide data and data related information to the grid whereas colModel provides information specific to the columns i.e titles, widths, data type (used for sorting), etc. Please check API section to see the comprehensive list of options which can be used in obj, dataModel and colModel.

<script>
$(function(){
    var data = [ [1,'Exxon Mobil','339,938.0','36,130.0'],
            [2,'Wal-Mart Stores','315,654.0','11,231.0'],
			[3,'Royal Dutch Shell','306,731.0','25,311.0'],
			[4,'BP','267,600.0','22,341.0'],
			[5,'General Motors','192,604.0','-10,567.0'],
			[6,'Chevron','189,481.0','14,099.0'],
			[7,'DaimlerChrysler','186,106.3','3,536.3'],
			[8,'Toyota Motor','185,805.0','12,119.6'],
			[9,'Ford Motor','177,210.0','2,024.0'],
			[10,'ConocoPhillips','166,683.0','13,529.0'],
			[11,'General Electric','157,153.0','16,353.0'],			
			[12,'Total','152,360.7','15,250.0'],				
			[13,'ING Group','138,235.3','8,958.9'],
			[14,'Citigroup','131,045.0','24,589.0'],
			[15,'AXA','129,839.2','5,186.5'],
			[16,'Allianz','121,406.0','5,442.4'],
			[17,'Volkswagen','118,376.6','1,391.7'],
			[18,'Fortis','112,351.4','4,896.3'],
			[19,'Crédit Agricole','110,764.6','7,434.3'],
			[20,'American Intl. Group','108,905.0','10,477.0']];
            
    var obj = {};
    obj.width = 700;
    obj.height = 400;
    obj.colModel = [{title:"Rank", width:100, dataType:"integer"},
        {title:"Company", width:200, dataType:"string"},
        {title:"Revenues ($ millions)", width:150, dataType:"float", align:"right"},
        {title:"Profits ($ millions)", width:150, dataType:"float", align:"right"}];
    obj.dataModel = {data:data};
    $("#grid_array").pqGrid( obj );                                

});        
</script>    

In the body tag define the div tag/tags which act as placeholders for the grid.

<div id="grid_array"></div>


How to load data

There exist a number of ways to load data in pqGrid which are based on open standards & RESTful architecture. At a top level, these can be categorized into 2 cases depending upon value of dataModel.location.

Case I: dataModel.location = 'local'

When dataModel.location is local, data can be assigned directly to the grid i.e., $grid.pqGrid( 'option', 'dataModel.data', data); where data is in format of array of rows. dataModel.location = 'local' doesn't necessarily mean local data. The data can reside locally in the browser within script tags or could be fetched from remote web server / service using synchronous or asynchronous AJAX calls. Assignment of data to grid is usually followed by a call to refreshDataAndView() in order to process and render data in the grid. Remote paging, remote sorting and remote filtering are bit tedious to implement when dataModel.location is local.

Case II: dataModel.location = 'remote'

When dataModel.location is remote, grid loads data on its own from remote location. There is no need to manually make an AJAX call and assign data to dataModel. refreshDataAndView() is called automatically by the grid whenever reload / refresh of data from remote server is required. In this case number of options have to be provided to pqGrid i.e., dataModel.url / dataModel.getUrl(), dataModel.getData(), dataModel.dataType, dataModel.method etc.

When dataModel.url is provided, grid constructs the query strings ( for GET requests ) or POST data on its own and send fields to the remote server at the specified url. The fields send by grid have prefix "pq_" for easy identification e.g., pq_curpage, pq_rpp, etc. Complete list of the name of parameters is provided in the next section. Own custom parameters can be added to the fields send by grid to the server with dataModel.postData and dataModel.postDataOnce options.

If full control is required to construct own query strings / POST data sent to server, it can be done with dataModel.getUrl() callback, though it's rarely required.

dataModel.getData() callback is called by the grid when remote data has arrived and ready to be processed by the grid. In this callback useful bits of data can be picked or incoming data can be transformed to make it suitable for grid consumption.



Remote requests

For remote paging, filtering & sorting, the grid sends the following fields of information to the server as GET or POST requests. Remote paging, filtering & sorting can be combined with each other.

TypeRequest variables
Name Value
Paging pq_datatype dataModel.dataType
pq_curpage pageModel.curPage
pq_rpp pageModel.rPP
Sorting pq_datatype dataModel.dataType
pq_sort sortModel.sorter e.g.,
pq_sort: [{"dataIndx":"ShipCountry","dir":"up"}, {"dataIndx":"ContactName","dir":"down"}]
Filtering pq_datatype dataModel.dataType
pq_filter Object variable having fields named "mode" and "data". The value of "mode" is taken from filterModel.mode and "data" is an array of objects constructed from individual column.filter e.g.,
pq_filter: { "mode": "AND", "data": [{"dataIndx":"ShipCountry","value":"fr","condition":"begin"}, {"dataIndx":"ContactName","value":"pa","condition":"begin"}]}

By default the fields are serialized to string with JSON.stringify before sending it to the server. Serialization of client requests is not required and is incompatible with some server side environments like PHP and it can be turned off by setting stringify option of pqGrid to false. As a side note, turning serialization on/off for different server environments holds true also for remote requests made during batch editing and auto save use cases.



How to refresh the data or view of grid

pqGrid tries to refresh view on its own prior to version 2.0.2 in most cases when dataModel properties are changed. This behaviour is sometimes undesirable e.g. consecutive calls to API changing dataModel properties may lead to undesirable refreshes and reloads of data which is not good for performance. This has changed starting from 2.0.2 whereby the data or view of grid can be refreshed in a more controlled manner. pqGrid can be refreshed in various ways depending upon what or which part of the grid needs to be refreshed. Please see the below comparison table to know which method to use:

refresh refreshView refreshDataAndView
Refresh Column Header Yes Yes Yes
Refresh view for updated rows Yes Yes Yes
Refresh view for added rows No Yes Yes
Refresh view for removed rows No Yes Yes
Reload data from server (location = 'remote') No No Yes
Reload data from dataModel.data (location = 'local') - Yes Yes
Refresh local paging No Yes Yes
Refresh local sorting No No Yes
Refresh local filtering No No Yes
Refresh row grouping No Yes Yes
Refresh cell merging No Yes Yes



Further Reading

How to use jQuery UI widgets

Copyright © 2012 - Paramvir Dhindsa (http://paramquery.com)