Skip to content

Instantly share code, notes, and snippets.

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

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

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