jQuery DataTables and Symfony

This article has moved to this location.


DataTables is a plug-in for the jQuery Javascript library. It is a highly flexible tool, based upon the foundations of progressive enhancement, which will add advanced interaction controls to any HTML table. DataTables offers a lot of features. For example, you can have on-the-fly filtering, ajax auto-loading of data, pagination, sorting columns, highlight sorted columns, extensive plug-in support, themeable by CSS or jQuery UI ThemeRoller an so on. For more details, you can read here.


How to use DataTables in symfony? here we go…

propel:
  country_classification:
    id: ~
    name:            { type: varchar(255), required: true }
    code:            { type: varchar(5), required: true }
    region:          { type: varchar(255) }
    income_group:    { type: varchar(50), required: true }
    lending_country: { type: varchar(5), required: true }
    other:           { type: varchar(5), required: true }

Data for this article is taken from this site. Remove empty and unneeded columns and convert it into csv file :

"name","code","region","income_group","lending_country","other"
"Afghanistan","AFG","South Asia","Low income","IDA","HIPC"
"Albania","ALB","Europe & Central Asia","Lower middle income","IBRD",
"Algeria","DZA","Middle East & North Africa","Upper middle income","IBRD",
...

Then export it into database using the task described here

  1. Create an action to show data for the first time :

    //actions.class.php
    public function executeIndex(sfWebRequest $request)
    {
    }
    
  2. Create the corresponding template (in this article is indexSuccess.php). The content of ‘tbody’ section is left empty, and let DataTables retrieve the data from the server.
    Notice, In order for DataTables to be able to function correctly, the DataTables expect the target table must be laid out in a well formed manner with the ‘thead’ and ‘tbody’ sections declared.

    <?php use_helper('jQuery') ?>
    <?php use_javascript('/jq-datatables/js/jquery.dataTables.min.js') ?>
    <?php use_stylesheet('/jq-datatables/css/demo_table.css', 'last') ?>
    <table id="country" style="width:100%">
      <thead>
        <tr>
          <th>Country</th>
          <th>Code</th>
          <th>Region</th>
        	<th>Income Group</th>
          <th>Lending Country</th>
          <th>Other</th>
        </tr>
      </thead>
      <tbody>
      </tbody>
    </table>
    <script type="text/javascript">
    jQuery(document).ready(function() {
      jQuery('#country').dataTable({
    	'bProcessing': true,
    	'bServerSide': true,
    	'sAjaxSource': "<?php echo url_for('classification_country/get_data') ?>"
     });
    })
    </script>
    

    The bServerSide parameter tells DataTables to do all the processing on the server side, and sAjaxSource parameter tells DataTables which URL that will provide the data.

  3. Now, create a function in your actions to retrieve data from server, and feed the client with well formed JSON array.

    public function executeGet_data(sfWebRequest $request)
    {
       sfConfig::set('sf_web_debug', false);
       $this->getResponse()->setContentType('application/json');
       $pager = CountryClassificationPeer::doSelectPager();
       $json = '{"iTotalRecords":'.$pager->getNbResults().',
         "iTotalDisplayRecords":'.$pager->getNbResults().',
         "aaData":[';
       $first = 0;     
       foreach ($pager->getResults() as $v)
       {
         if ($first++) $json .= ',';
         $json .= '["'.$v->getName().'",
        	"'.$v->getCode().'",
            "'.$v->getRegion().'",
            "'.$v->getIncomeGroup().'",
            "'.$v->getLendingCountry().'",
            "'.$v->getOther().'"]'; 
       }
       $json .= ']}';
       return $this->renderText($json);    
    }
    

    The Resulting JSON array has three parameters:

    • iTotalRecords is the total number of records in the database
    • iTotalDisplayRecords is the total number after filtering
    • aaData is a two-dimensional array of data that corresponds to the rows and columns of the shown table
  4. In CountryClassificationPeer.php, create function to retrieve and paginate the results.

    public static function doSelectPager($page=1, $item_per_page = 10, Criteria $criteria = null)
    {
      if ($criteria === null) 
      {
        $criteria = new Criteria();
      }
      $pager = new sfPropelPager('CountryClassification', $item_per_page);
      $pager->setCriteria($criteria);
      $pager->setPage($page);
      $pager->setPeerMethod('doSelect');
      $pager->init();
      return $pager;
    }
    
  5. When this is viewed in the browser, we immediately have a working data table as in the picture above, but without paging, sorting and searching features.

Ok.. now let’s add features such as paging, sorting and searching. But, before we implement these features, we have to know the parameters that are sent by the DataTables per request.

  • iDisplayStart: Where to paginate from.
  • iDisplayLength: Number of rows that are visible.
  • sSearch: String to search globally for.
  • iSortingCol_(int): Number of columns to sort by.
  • sSortDir_(int): Direction to sort in.

For other parameters, please refer to the DataTables documentation.

Pagination

Parameters needed for the pagination are iDisplayStart and iDisplayLength. The default values for these parameters are 0 and 10 respectively. In order for pagination working in symfony, we have to define the page value, that needed by sfPropelPoger to working on. But, how to obtain it? Divide iDisplayStart by iDisplayLength, and increase the result by one.
Let’s modify executeGet_data() function. Insert following code after $this->getResponse()->setContentType(‘application/json’)

   //start: paging
   $item_per_page = $request->getParameter('iDisplayLength', 10);
   $page = ($request->getParameter('iDisplayStart', 0) / $item_per_page) + 1;
   //end: paging
   $pager = CountryClassificationPeer::doSelectPager($page, $item_per_page);

DataTables provide two types of pagination styles, two_button (default) and full_numbers style.

Sorting

Parameters needed for sorting are:

  • iSortCol_0: This defines the first column to sort. The value is numerical, starting with 0, and corresponds to what columns are displayed in the table, not the fields in the database.
  • sSortDir_0: This defines the direction the column should be sorted in (asc or desc).

Modify executeGet_data() function, and insert following code after //end: paging

  //start: sorting
  $type_colnames = array(CountryClassificationPeer::NAME, CountryClassificationPeer::CODE, CountryClassificationPeer::REGION, CountryClassificationPeer::INCOME_GROUP, CountryClassificationPeer::LENDING_COUNTRY, CountryClassificationPeer::OTHER);
  $iSortCol_0 = $request->getParameter('iSortCol_0');
  if($iSortCol_0 > max(array_keys($type_colnames)) || $iSortCol_0 < 0) $iSortCol_0 = 0;
  $c = new Criteria();
  if ('asc' === $request->getParameter('sSortDir_0', 'asc'))
  {
     $c->addAscendingOrderByColumn($type_colnames[$iSortCol_0]);
  }
  else
  {
     $c->addDescendingOrderByColumn($type_colnames[$iSortCol_0]);
  }
  //end: sorting
  $pager = CountryClassificationPeer::doSelectPager($page, $item_per_page, $c);

variable type_colnames is an array propel column type that corresponds to the columns displayed in the table

Searching with Zend Lucene

First, download the Zend Framework and un-archive the files so that you have a lib/vendor/Zend/ directory.
Then, add the following code to the ProjectConfiguration class to provide a simple way to register the Zend autoloader:

// config/ProjectConfiguration.class.php
class ProjectConfiguration extends sfProjectConfiguration
{
  static protected $zendLoaded = false;
  static public function registerZend()
  {
    if (self::$zendLoaded)
    {
      return;
    }
    set_include_path(sfConfig::get('sf_lib_dir').'/vendor'.PATH_SEPARATOR.get_include_path());
    require_once sfConfig::get('sf_lib_dir').'/vendor/Zend/Loader/Autoloader.php';
    Zend_Loader_Autoloader::getInstance();
    self::$zendLoaded = true;
  }
  // ...
}

Open CountryClassificationPeer.php, add the functions below :

static public function getLuceneIndex()
{
   ProjectConfiguration::registerZend();
   if (file_exists($index = self::getLuceneIndexFile()))
   {
     return Zend_Search_Lucene::open($index);
   }
   else
   {
     return Zend_Search_Lucene::create($index);
   }
}
static public function getLuceneIndexFile()
{
  return sfConfig::get('sf_cache_dir').'/country_class/country_class.index';
}
static public function getCriteriaForLuceneQuery($query, Criteria $c = null)
{
   $hits = self::getLuceneIndex()->find($query);
   $pks = array();
   foreach ($hits as $hit)
   {
     $pks[] = $hit->pk;
   }
   $c = (null === $c) ? new Criteria() : clone $c;
   $c->add(self::ID, $pks, Criteria::IN);
   return $c;
}

Open CountryClassification.php, add functions below:

public function updateLuceneIndex()
{
  $index = CountryClassificationPeer::getLuceneIndex();
  foreach ($index->find('pk:'.$this->getId()) as $hit)
  {
    $index->delete($hit->id);
  }
  $doc = new Zend_Search_Lucene_Document();
  $doc->addField(Zend_Search_Lucene_Field::Keyword('pk', $this->getId()));
  $doc->addField(Zend_Search_Lucene_Field::UnStored('name', $this->getName(), 'utf-8'));
  $doc->addField(Zend_Search_Lucene_Field::UnStored('region', $this->getRegion(), 'utf-8'));
  $doc->addField(Zend_Search_Lucene_Field::UnStored('income_group', $this->getIncomeGroup(), 'utf-8'));
  $doc->addField(Zend_Search_Lucene_Field::UnStored('lending_country', $this->getLendingCountry(), 'utf-8'));
  $index->addDocument($doc);
  $index->commit();
}
public function delete(PropelPDO $con = null)
{
  $index = CountryClassificationPeer::getLuceneIndex();
  foreach ($index->find('pk:'.$this->getId()) as $hit)
  {
    $index->delete($hit->id);
  }
  return parent::delete($con);
}
public function save(PropelPDO $con = null)
{
  f (is_null($con))
  {
    $con = Propel::getConnection(CountryClassificationPeer::DATABASE_NAME, Propel::CONNECTION_WRITE);
  }
  $con->beginTransaction();
  try
  {
    $affectedRows = parent::save($con);
    $this->updateLuceneIndex();
    $con->commit();
    return $affectedRows;
  }
  catch (Exception $e)
  {
    $con->rollback();
    throw $e;
  }
}

Modify executeGet_data() function, and insert following code after //end: sorting

  //start: searching
  if ($query = $request->getParameter('sSearch'))
  {
    $c = CountryClassificationPeer::getCriteriaForLuceneQuery($query, $c);
  }		
  //end: searching

Now, every time you type in search box, a request is sent to the server.

Setting a delay on the filter

To avoid overloading your server, you need to query the data only when you’ve actually finished typing.
The DataTables plugin itself can be extended with further plugins, one of which is called fnSetFilteringDelay. To include it, simply copy and paste from the DataTables plugins page into your template (in this article is indexSuccess.php), above the jQuery(document).ready section, and then activate it by chaining it to your dataTable call.

<script type="text/javascript">
jQuery.fn.dataTableExt.oApi.fnSetFilteringDelay = function ( oSettings, iDelay ) { 
  var _that = this;
  this.each( function ( i ) {
    $.fn.dataTableExt.iApiIndex = i;
    var	iDelay = (iDelay && (/^[0-9]+$/.test(iDelay)) ? iDelay : 250),
    $this = this,  oTimerId = null, sPreviousSearch = null,
    anControl = $( 'div.dataTables_filter input:text' );
    anControl.unbind( 'keyup' ).bind( 'keyup', function() {
    var $$this = $this;
    if (sPreviousSearch === null || sPreviousSearch != anControl.val()) {
      window.clearTimeout(oTimerId);
      sPreviousSearch = anControl.val();	
      oTimerId = window.setTimeout(function() {
        $.fn.dataTableExt.iApiIndex = i;
	_that.fnFilter( anControl.val() );
      }, iDelay);
    }
  });
  return this;
} );
return this;
};
 
jQuery(document).ready(function() {
  jQuery('#country').dataTable({
	'bProcessing': true,
	'bServerSide': true,
	'sAjaxSource': "<?php echo url_for('classification_country/get_data') ?>"
 }).fnSetFilteringDelay();
})
</script>

Below is the full source code for executeGet_data() function

public function executeGet_data(sfWebRequest $request)
{
  sfConfig::set('sf_web_debug', false);
  $this->getResponse()->setContentType('application/json');
  //start: paging
  $item_per_page = $request->getParameter('iDisplayLength', 10);
  $page = ($request->getParameter('iDisplayStart', 0)/$item_per_page)+1;
  //end: paging
  //start: sorting
  $type_colnames = array(CountryClassificationPeer::NAME, CountryClassificationPeer::CODE, CountryClassificationPeer::REGION, CountryClassificationPeer::INCOME_GROUP, CountryClassificationPeer::LENDING_COUNTRY, CountryClassificationPeer::OTHER);
  $iSortCol_0 = $request->getParameter('iSortCol_0');
  if($iSortCol_0 > max(array_keys($type_colnames)) || $iSortCol_0 < 0) $iSortCol_0 = 0;
  $c = new Criteria();
  if ('asc' === $request->getParameter('sSortDir_0', 'asc'))
  {
    $c->addAscendingOrderByColumn($type_colnames[$iSortCol_0]);
  }
  else
  {
    $c->addDescendingOrderByColumn($type_colnames[$iSortCol_0]);
  }
  //end: sorting
  //start: searching
  if ($query = $request->getParameter('sSearch'))
  {
    $c = CountryClassificationPeer::getCriteriaForLuceneQuery($query, $c);
  }		
  //end: searching
		
 $pager = CountryClassificationPeer::doSelectPager($page, $item_per_page, $c);
 $json = '{"iTotalRecords":'.$pager->getNbResults().',
   "iTotalDisplayRecords":'.$pager->getNbResults().',
   "aaData":[';
 $first = 0;     
 foreach ($pager->getResults() as $v)
 {
   if ($first++) $json .= ',';
   $json .= '["'.$v->getName().'",
     "'.$v->getCode().'",
     "'.$v->getRegion().'",
     "'.$v->getIncomeGroup().'",
     "'.$v->getLendingCountry().'",
     "'.$v->getOther().'"]'; 
 }
 $json .= ']}';
 return $this->renderText($json);    
}

Hope it useful..

Update :
We can use built in php json function to create the json string:

$aaData = array();
foreach ($pager->getResults() as $v)
{
   $aaData[] = array($v->getName(), $v->getCode(), $v->getRegion(), $v->getIncomeGroup(), $v->getLendingCountry(), $v->getOther());
}
$jsonArr = array("iTotalRecords"=>$pager->getNbResults(), "iTotalDisplayRecords"=>$pager->getNbResults(), "aaData"=>$aaData);
return $this->renderText(json_encode($jsonArr));

References:

  1. Data Tables and DataTables Plugin in jQuery 1.3 with PHP
  2. jQuery DataTables
  3. Practical symfony Day 17: Search
Advertisements

18 responses to “jQuery DataTables and Symfony

  1. megharaj April 11, 2011 at 12:21 pm

    Hi i want to know how to use the server side sorting and searching using datatables .

  2. kumar April 14, 2011 at 1:00 pm

    how to integrate data tables server side sorting and searching in symfony. Little confusing to follow these steps .
    What is this CountryClassification.php

  3. megharaj April 21, 2011 at 12:20 pm

    Hi,
    It’ s really good site for server side and client side sorting which helps how to integrate in our project.
    The sorting, searching functionality can easily implement in our project.
    Thanks,
    Megharaj

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: