DataTable server side for Symfony 2 Doctrine

src\Acme\UserBundle\Entity\UserRepository.php

<?php

namespace Acme\UserBundle\Entity;

use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr;

/**
 * UserRepository
 *
 * This class was generated by the Doctrine ORM. Add your own custom
 * repository methods below.
 */
class UserRepository extends EntityRepository
{

  /**
   * @param array $get
   * @param bool $flag
   * @return array|\Doctrine\ORM\Query
   */
  public function ajaxTable($get, $flag = false){

    /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
       * Easy set variables
       */

    /* Indexed column (used for fast and accurate table cardinality) */
    $alias = "a";

    /* DB table to use */
    $tableObjectName = 'UserBundle:User';

    /* Array of database columns which should be read and sent back to DataTables. Use a space where
       * you want to insert a non-database field (for example a counter or static image)
       */
    $columns = array( 'id', 'email', 'username', 'twitterID' );


    $aColumns = array();
    foreach($columns as $value) $aColumns[] = $alias .'.'. $value;

    $cb = $this->getEntityManager()
      ->getRepository($tableObjectName)
      ->createQueryBuilder($alias)
      ->select(str_replace(" , ", " ", implode(", ", $aColumns)));

    if ( isset( $get['iDisplayStart'] ) && $get['iDisplayLength'] != '-1' ){
      $cb->setFirstResult( (int)$get['iDisplayStart'] )
        ->setMaxResults( (int)$get['iDisplayLength'] );
    }

    /*
     * Ordering
     */
    if ( isset( $get['iSortCol_0'] ) ){
      for ( $i=0 ; $i<intval( $get['iSortingCols'] ) ; $i++ ){
        if ( $get[ 'bSortable_'.intval($get['iSortCol_'.$i]) ] == "true" ){
          $cb->orderBy($aColumns[ (int)$get['iSortCol_'.$i] ], $get['sSortDir_'.$i]);
        }
      }
    }

    /*
       * Filtering
       * NOTE this does not match the built-in DataTables filtering which does it
       * word by word on any field. It's possible to do here, but concerned about efficiency
       * on very large tables, and MySQL's regex functionality is very limited
       */
    if ( isset($get['sSearch']) && $get['sSearch'] != '' ){
      $aLike = array();
      for ( $i=0 ; $i<count($aColumns) ; $i++ ){
        if ( isset($get['bSearchable_'.$i]) && $get['bSearchable_'.$i] == "true" ){
          $aLike[] = $cb->expr()->like($aColumns[$i], '\'%'. $get['sSearch'] .'%\'');
        }
      }
      if(count($aLike) > 0) $cb->andWhere(new Expr\Orx($aLike));
      else unset($aLike);
    }

    /*
     * SQL queries
     * Get data to display
     */
    $query = $cb->getQuery();
    $rResult = $query->getArrayResult();

    /* Data set length after filtering */
    $iFilteredTotal = count($rResult);

    /* Total data set length */
    $aResultTotal = $this->getEntityManager()
      ->createQuery('SELECT COUNT('. $alias .') FROM '. $tableObjectName .' '.$alias)
      ->setMaxResults(1)
      ->getResult();
    $iTotal = $aResultTotal[0][1];


    /*
     * Output
     */
    $output = array(
      "sEcho" => intval($get['sEcho']),
      "iTotalRecords" => $iTotal,
      "iTotalDisplayRecords" => $iFilteredTotal,
      "aaData" => array()
    );

    foreach($rResult as $aRow)
    {
      $row = array();
      for ( $i=0 ; $i<count($columns) ; $i++ ){
        if ( $columns[$i] == "version" ){
          /* Special output formatting for 'version' column */
          $row[] = ($aRow[ $columns[$i] ]=="0") ? '-' : $aRow[ $columns[$i] ];
        }elseif ( $columns[$i] != ' ' ){
          /* General output */
          $row[] = $aRow[ $columns[$i] ];
        }
      }
      $output['aaData'][] = $row;
    }

    return $output;
  }

src\Acme\YuhrBundle\Controller\AjaxController.php

<?php

namespace Antalika\AdminBundle\Controller;

use Symfony\Bundle\FrameworkBundle\Controller\Controller;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Template;

use Doctrine\ORM\PersistentCollection;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;

/**
 * @Route("/ajax")
 */
class AjaxController extends Controller
{

  /**
   * @Route("/users/list", name="admin_ajax_users_list")
   * @Template()
   */
  public function usersListAction(Request $request)
  {
    $get = $request->query->all();

    $em = $this->getDoctrine()->getEntityManager();
    $rResult = $em->getRepository('UserBundle:User')->ajaxTable($get, true);

    return new Response(
      json_encode($rResult)
    );
  }


}

http://datatables.net/release-datatables/examples/data_sources/server_side.html

http://datatables.net/forums/discussion/11016/datatable-server-side-for-symfony-2-doctrine#Item_1

I fixed my paging problem this morning like this :

In my repository, i passed $get by reference and added :

$get['iFilteredTotal'] = count($cb
->select($alias.'.id')
->setFirstResult(null)
->setMaxResults(null)
->getQuery()->getResult());

In the ajax controller, i added :

if ( array_key_exists('iFilteredTotal',$get) )
{
$iFilteredTotal = $get['iFilteredTotal'];
} else {
$iFilteredTotal = $iTotalRecords;
}

and in $output :

"iTotalDisplayRecords" => $iFilteredTotal,

Hope it will help.

hi @Slauta, really very helpful article, I also would like to know how to get associated entities ?