DataTable server side for Symfony 2 Doctrine (ver 2)

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(array $get, $flag = false){

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

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

    /**
     * Set to default
     */
    if(!isset($get['columns']) || empty($get['columns']))
      $get['columns'] = array('id');

    $aColumns = array();
    foreach($get['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();

    if($flag)
      return $query;
    else
      return $query->getResult();
  }

  /**
   * @return int
   */
  public function getCount(){
    $aResultTotal = $this->getEntityManager()
      ->createQuery('SELECT COUNT(a) FROM UserBundle:User a')
      ->setMaxResults(1)
      ->getResult();
     return $aResultTotal[0][1];
  }

}

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();

    /* 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', 'twitter_username', 'twitterID', 'firstname' );
    $get['columns'] = &$columns;

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

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

    /*
     * Output
     */
    $output = array(
      "sEcho" => intval($get['sEcho']),
      "iTotalRecords" => $em->getRepository('UserBundle:User')->getCount(),
      "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;
    }

    unset($rResult);

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


}

hello, thank you very much for your great contribution, but I followed everything to the letter, the problem is that the pagination does not work. everything else if.

I hope you can help, I would greatly appreciate it.

you have any idea why this happens? thanks

Hello, thanx for your code, it really helped me.
I figured out solution about pagination issue.
In $output array to "iTotalDisplayRecords" assigned $iFilteredTotal which actually contains number of rows to display at table's current page, but it must be count of rows after filtering.

As written in documentation: (int) iTotalDisplayRecords - Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned in this result set).

So my fast and dirty solution is to add this function to repository: https://gist.github.com/asagajda/5973304
Than in controller make following correction:
"iTotalDisplayRecords" => $em->getRepository('Entity')->getFilteredCount($get),

Hello,
I'v implemented the code like, but it seems some exceptions thrown :
" The controller must return a response (null given). Did you forget to add a return statement somewhere in your controller?
500 Internal Server Error - LogicException "

for relational table

[Semantical Error] line 0, col 47 near 'createdBy FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

@noufel12 the exception is self-explaining - your controller does not return any response.

Boy, this is awesome. I was looking for something like this. Thank you for the contribution.