I need to manage the displaying a big amount of records that comes from a Doctrine Paginator query with jQuery DataTables. Now, I'm looking for the best method to do this task.
In terms of performance, it is better to paginate the results at database level, so you can avoid the overhead of mapping the results to objects, decrease the network traffic and your database server wont need to read/filter that many records [...] @Xocoatzin on this comment
I have written a Symfony controller action to retrieve Account's data from database:
AccountController
/**
* Lists all Account entities.
*
* @Route("/list/{page}", defaults={"page" = 1}, name="account_index")
* @Template("account/index.html.twig")
* @Method("GET")
*/
public function indexAction($page)
{
$limit = 15;
$offset = ($page - 1) * $limit;
$accountRepo = $this->getDoctrine()->getRepository('SupportBundle:Account');
$query = $accountRepo->createQueryBuilder('a')
->setFirstResult($offset)
->setMaxResults($limit)
->getQuery();
$paginator = new Paginator($query);
dump((int )$paginator->count());
$total = (int) ceil($paginator->count() / $limit);
dump($total);
$accounts = $query->getResult();
return array(
'accounts' => $accounts,
'title' => 'Accounts list',
'paginator' => $paginator->getIterator(),
'pages' => $total,
'current_page' => $page
);
}
For the rendering of the view I have implemented a simple Twig template with a for that fill my table.
I've also found this gist for Symfony2/DataTables integration.
I'm using:
- Symfony 2.8.2
- jQuery 1.12
- DataTables 1.10.8
I found several paginator bundles for Symfony, but my intent is to maintain a simple approach starting from scratch with only Doctrine paginator, so I will be able to change in the future without depending on third party components. I think it will be also better for compatibility and decoupling.
Paginator Symfony components:
