Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save Slauta/3175282 to your computer and use it in GitHub Desktop.
Save Slauta/3175282 to your computer and use it in GitHub Desktop.
DataTable server side for Symfony 2 Doctrine (ver 2)
<?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];
}
}
<?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)
);
}
}
@aosmialowski
Copy link

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

@manytostao-zz
Copy link

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

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment