Home > Development > jqGrid

jqGrid

jqGrid is an excellent data grid plugin for jQuery which I wanted to use in my TV Schedule application. It went very smoothly except for the order in which the columns are displayed. jqGrid will display the columns in the order that it receives them. This means that you would have to define the column order in your data service. I did not want to do this because if the UI wanted to change the order it would have no way of doing it. My solution was to pass back an array of columns in the order that I wanted. I use this to sort my data. Not perfect but at least the sort is defined in the jqGrid definition.

In addition I have to convert the data from my model (API or DB) to an object in the format that jqGrid expects that I then json_encode to pass back.

The backend in the case is PHP(cakePHP).

Here is the jqGrid definition.

    jQuery('#ppvList').jqGrid({
        url: '/tv_schedules/get_ppv_list',
        mtype: 'POST',
        datatype: 'json',
	    // This determines the order of columns.  It MUST match the colModel
		postData: {colOrder: 'title,genre,rating,price'},
		colModel: [
					{label: 'Title', name: 'title', index: 'title', sortable:true},
					{label: 'Genre', name: 'genre', index: 'genre', sortable:true , width: '50'},
					{label: 'Rating', name: 'rating',index: 'rating', sortable:true , width: '50'},
					{label: 'Price', name: 'price', index: 'price', sortable:true, width: '25', align: 'right'}
				],
        pager: jQuery('#pager'),
        rowNum: 10,
        rowList: [10, 20, 30],
        sortname: 'title',
        sortorder: 'asc',
        viewrecords: true,
		autowidth: true,
		height: 'auto'
 
    });

Here is the data converter.

class DataPrepComponent extends Object
{
 
	/*
	 * This method converts an array in the following format to an object that can be
	 * JSON encoded and passed to jqGrid
	 *
	 *  Array
		(
		    [0] => Array
		        (
		            [ModelName] => Array
		                (
		                    [id] => 83
		                    [field1] => value1
		                    [field2] => value2
		                    [field3] => value3
		                    [field4] => value4
		                    [field5] => value5
		                )
		    [1] => Array
		        (
		            [ModelName] => Array
		                (
		                    [id] => 84
		                    [field1] => value1
		                    [field2] => value2
		                    [field3] => value3
		                    [field4] => value4
		                    [field5] => value5
		                )
 
		)
 
		This array will be converted to the following for jQgrid.
 
		object(stdClass)
		  public 'total' => int
		  public 'page' => int
		  public 'records' => int
		  public 'rows' =>
		    array
		      0 =>
		        object(stdClass)[20]
		          public 'id' => string
		          public 'cell' =>
		            array
		              0 => string
		              1 => string
		              2 => string
		              3 => string
		              4 => string
		      1 =>
		        object(stdClass)
		          public 'id' => string
		          public 'cell' =>
		            array
		              0 => string
		              1 => string
		              2 => string
		              3 => string
		              4 => string 
 
	 */
	public function prep_jqgrid(Array $data, StdClass $pager, Array $col_order)
	{
		//$this->log($data, LOG_DEBUG);
		/*
			THESE ARE THE PARAMETERS SENT BY JQGRID FOR THE PAGER
 
			total	total pages for the query
			page	current page of the query
			records	total number of records for the query
			rows	an array that contains the actual data
				id	the unique id of the row
				cell	an array that contains the data for a row
		*/
		$jqgrid_data = new StdClass();
		$jqgrid_data->total = $pager->total;
		$jqgrid_data->page = $pager->page;
		$jqgrid_data->records = $pager->records;
		$jqgrid_data->rows = array();
 
		foreach($data as $model)
		{
			$row = new stdClass();
			foreach($model as $model_name => $db_row)
			{
				$row->id = $db_row['id'];
				unset($db_row['id']);
				foreach($col_order as $col)
				{
					$row->cell[] = $db_row[$col];
				}
			}
			$jqgrid_data->rows[] = $row;
		}
		return $jqgrid_data;
	}
}

Here is how I call this:

	function get_ppv_list()
	{
       //	Configure::write('debug', 0);
	    $this->pageTitle = 'Order PPV/VOD';
		$this->loadModel('TvSchedule');
 
		$order = 'TvSchedule.title DESC';
        if (isset($this->params['form']['sidx']))
        {
            $order = 'TvSchedule.' . strtolower($this->params['form']['sidx']) . ' ' . $this->params['form']['sord'];
        }
 
        $params = array (
        //'conditions'=> array ('Model.field'=>$thisValue), //array of conditions
        //'recursive'=>1, //int
        'fields'=> array ('TvSchedule.id', 'TvSchedule.title', 'TvSchedule.genre', 'TvSchedule.rating', 'TvSchedule.price'), //array of field names
        'order'=> array ($order), //string or array defining order
        //'group'=> array ('Model.field'), //fields to GROUP BY
        'limit'=> $this->params['form']['rows'], //int
        'page'=> $this->params['form']['page'] //int
        //'callbacks'=>true //other possible values are false, 'before', 'after'
        );
 
		$record_total = $this->TvSchedule->find('count');
 
		$pager->total = ceil($record_total/$this->params['form']['rows']);
		$pager->page = $this->params['form']['page'];
		$pager->records = $record_total;
 
		$this->set('PPVdata', json_encode($this->DataPrep->prep_jqgrid($this->TvSchedule->find('all', $params), $pager, split(',', $this->params['form']['colOrder']))));
		$this->render('ppv_list', 'ajax');
	}

, ,

Leave a Reply

Trackbacks:0

Listed below are links to weblogs that reference
jqGrid from rhythmicalmedia.com
TOP