If you're using KnpPaginatorBundle or Doctrine Pagination to paginate query results, you will end up seeing an eye watering query in your doctrine query log just for getting the total records. Query example below is just a stripped down version so imagine you have plenty of joins in your query and plenty of data in DB. There are two problems with it. The first one is, it will be slow. The second one is, you'll get 500 Internal Server Error in some cases for your requests. That happened to me! To solve this issue, use manual pagination yourself like below.


Eye watering query


SELECT COUNT(*) AS dctrn_count
FROM (
SELECT DISTINCT id0
FROM (
SELECT s0_.id AS id0, s0_.name AS name1
FROM student s0_
) dctrn_result
)
dctrn_table [] []

Dummy data


ID	NAME
1 Robert
2 DeNiro
3 Inanzzz
4 Al
5 Pacino
6 Inanzzz
7 Andy
8 Garcia
9 Inanzzz

Controllers.yml


services:
services:
application_backend.controller.abstract:
class: Application\BackendBundle\Controller\AbstractController
abstract: true
arguments:
- @serializer
- @validator
- @doctrine_common_inflector

application_backend.controller.student:
class: Application\BackendBundle\Controller\StudentController
parent: application_backend.controller.abstract
arguments:
- @application_backend.service.student

doctrine_common_inflector:
class: Doctrine\Common\Inflector\Inflector

Services.yml


services:
application_backend.service.student:
class: Application\BackendBundle\Service\StudentService
arguments:
- @application_backend.repository.student
- @application_backend.factory.student

Repositories.yml


services:
application_backend.repository.student:
class: Application\BackendBundle\Repository\StudentRepository
factory: [@doctrine.orm.entity_manager, getRepository]
arguments: [Application\BackendBundle\Entity\Student]

Factories.yml


services:
application_backend.factory.student:
class: Application\BackendBundle\Factory\StudentFactory

AbstractController.php


namespace Application\BackendBundle\Controller;

use Doctrine\Common\Inflector\Inflector;
use JMS\Serializer\SerializationContext;
use JMS\Serializer\SerializerInterface;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Validator\ConstraintViolationInterface;
use Symfony\Component\Validator\ConstraintViolationList;
use Symfony\Component\Validator\ConstraintViolationListInterface;
use Symfony\Component\Validator\Validator\ValidatorInterface;

abstract class AbstractController
{
private $validContentTypes = ['json' => 'application/json', 'xml' => 'application/xml'];

protected $serializer;
protected $validator;
protected $inflector;

public function __construct(
SerializerInterface $serializer,
ValidatorInterface $validator,
Inflector $inflector
) {
$this->serializer = $serializer;
$this->validator = $validator;
$this->inflector = $inflector;
}

/**
* @param string $contentType
*
* @return string|Response
*/
protected function validateContentType($contentType)
{
if (!in_array($contentType, $this->validContentTypes)) {
return $this->createFailureResponse(
['content_type' => sprintf('Invalid content type [%s].', $contentType)],
'json',
415
);
}

return array_search($contentType, $this->validContentTypes);
}

/**
* @param string $payload
* @param string $model
* @param string $format
*
* @return object|Response
*/
protected function validatePayload($payload, $model, $format)
{
$payload = $this->serializer->deserialize($payload, $model, $format);

$errors = $this->validator->validate($payload);
if (count($errors)) {
return $this->createFailureResponse($errors, $format);
}

return $payload;
}

/**
* @param array|object $content
* @param string $format
* @param int $status
*
* @return Response
*/
protected function createSuccessResponse($content, $format = 'json', $status = 200)
{
return $this->getResponse($content, $format, $status);
}

/**
* @param array|ConstraintViolationListInterface $content
* @param string $format
* @param int $status
*
* @return Response
*/
protected function createFailureResponse($content, $format = 'json', $status = 400)
{
$errorList = null;

if ($content instanceof ConstraintViolationList) {
foreach ($content as $error) {
$error = $this->getErrorFromValidation($error);
$errorList[$error['key']] = $error['value'];
}
} else {
$errorList = $content;
}

return $this->getResponse(['errors' => $errorList], $format, $status);
}

/**
* @param array|object $content
* @param string $format
* @param int $status
*
* @return Response
*/
private function getResponse($content, $format, $status)
{
$context = new SerializationContext();
$context->setSerializeNull(false);

$response = $this->serializer->serialize($content, $format, $context);

return new Response($response, $status, ['Content-Type' => $this->validContentTypes[$format]]);
}

/**
* @param ConstraintViolationInterface $error
*
* @return array
*/
private function getErrorFromValidation($error)
{
$properties = $this->inflector->tableize($error->getPropertyPath());

return ['key' => $properties, 'value' => $error->getMessage()];
}
}

StudentController.php


namespace Application\BackendBundle\Controller;

use Application\BackendBundle\Service\StudentServiceInterface;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Route;
use Sensio\Bundle\FrameworkExtraBundle\Configuration\Method;
use JMS\Serializer\SerializerInterface;
use Symfony\Component\HttpFoundation\Request;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Validator\Validator\ValidatorInterface;
use Doctrine\Common\Inflector\Inflector;

/**
* @Route("student", service="application_backend.controller.student")
*/
class StudentController extends AbstractController
{
private $studentService;

public function __construct(
SerializerInterface $serializer,
ValidatorInterface $validator,
Inflector $inflector,
StudentServiceInterface $studentService
) {
parent::__construct($serializer, $validator, $inflector);

$this->studentService = $studentService;
}

/**
* @param Request $request
*
* @Route("")
* @Method({"GET"})
*
* @return Response
*/
public function indexAction(Request $request)
{
$name = $request->query->get('name');
$page = $request->query->get('page');
$limit = $request->query->get('limit');

$result = $this->studentService->get($name, $page, $limit);

return $this->createSuccessResponse($result);
}
}

StudentServiceInterface.php


namespace Application\BackendBundle\Service;

use Application\BackendBundle\Model\Student\Result;

interface StudentServiceInterface
{
/**
* @param string $name
* @param int $page
* @param int $limit
*
* @return Result
*/
public function get($name, $page, $limit);
}

StudentService.php


namespace Application\BackendBundle\Service;

use Application\BackendBundle\Factory\StudentFactoryInterface;
use Application\BackendBundle\Model\Student\Result;
use Application\BackendBundle\Repository\StudentRepository;
use Application\BackendBundle\Util\PagerTrait;

class StudentService implements StudentServiceInterface
{
use PagerTrait;

private $studentRepository;
private $studentFactory;

public function __construct(
StudentRepository $studentRepository,
StudentFactoryInterface $studentFactory
) {
$this->studentRepository = $studentRepository;
$this->studentFactory = $studentFactory;
}

/**
* @param string $name
* @param int $page
* @param int $limit
*
* @return Result
*/
public function get($name, $page, $limit)
{
$page = $this->getPage($page);
$limit = $this->getLimit($limit);
$offset = $this->getOffset($page, $limit);
$total = 0;

$result = $this->studentRepository->findPaginatedByName($name, $limit, $offset);
if ($result) {
$total = $this->studentRepository->findPaginatedByNameCount($name);
}

return $this->studentFactory->createStudentResult($result, $name, $page, $limit, $total);
}
}

StudentFactoryInterface.php


namespace Application\BackendBundle\Factory;

use Application\BackendBundle\Model\Student\Result;

interface StudentFactoryInterface
{
/**
* @param array $result
* @param string $keyword
* @param int $page
* @param int $limit
* @param int $total
*
* @return Result
*/
public function createStudentResult(array $result = [], $keyword, $page, $limit, $total);
}

StudentFactory.php


namespace Application\BackendBundle\Factory;

use Application\BackendBundle\Model\Student\Result;
use Application\BackendBundle\Model\Student\Student;

class StudentFactory implements StudentFactoryInterface
{
/**
* @param array $result
* @param string $keyword
* @param int $page
* @param int $limit
* @param int $total
*
* @return Result
*/
public function createStudentResult(array $result = [], $keyword, $page, $limit, $total)
{
$studentResult = new Result();
$studentResult->keyword = $keyword;
$studentResult->page = $page;
$studentResult->limit = $limit;
$studentResult->total = $total;
$studentResult->students = $this->getStudents($result);

return $studentResult;
}

/**
* @param array $result
*
* @return array
*/
private function getStudents(array $result = [])
{
$students = [];

foreach ($result as $data) {
$student = new Student();
$student->id = $data->getId();
$student->name = $data->getName();
$students[] = $student;
}

return $students;
}
}

StudentRepository.php


namespace Application\BackendBundle\Repository;

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

class StudentRepository extends EntityRepository
{
/**
* @param string|null $name
* @param int $limit
* @param int $offset
*
* @returns array
*/
public function findPaginatedByName($name, $limit, $offset)
{
$qb = $this->createQueryBuilder('s');

if ($name) {
$qb->where('s.name = :name')->setParameter('name', $name);
}

$qb->setMaxResults($limit)->setFirstResult($offset);

return $qb->getQuery()->getResult(Query::HYDRATE_SIMPLEOBJECT);
}

/**
* @param string|null $name
*
* @return int
*/
public function findPaginatedByNameCount($name)
{
$qb = $this->createQueryBuilder('s')->select('COUNT(s)');

if ($name) {
$qb->where('s.name = :name')->setParameter('name', $name);
}

return $qb->getQuery()->getResult(Query::HYDRATE_SINGLE_SCALAR);
}
}

Student.php entity


namespace Application\BackendBundle\Entity;

use Doctrine\ORM\Mapping as ORM;

/**
* @ORM\Entity(repositoryClass="Application\BackendBundle\Repository\StudentRepository")
* @ORM\Table(name="student")
*/
class Student
{
/**
* @ORM\Id
* @ORM\Column(type="integer")
* @ORM\GeneratedValue(strategy="AUTO")
*/
private $id;

/**
* @ORM\Column(name="name", type="string", length=20)
*/
private $name;
}

Student.php model


namespace Application\BackendBundle\Model\Student;

class Student
{
/**
* @var int
*/
public $id;

/**
* @var string
*/
public $name;
}

Result.php model


namespace Application\BackendBundle\Model\Student;

class Result
{
/**
* @var Student[]
*/
public $students = [];

/**
* @var string
*/
public $keyword;

/**
* @var int
*/
public $page;

/**
* @var int
*/
public $limit;

/**
* @var int
*/
public $total;
}

PagerTrait.php


namespace Application\BackendBundle\Util;

trait PagerTrait
{
public function getPage($page = 1)
{
if ($page < 1) {
$page = 1;
}

return floor($page);
}

public function getLimit($limit = 20)
{
if ($limit < 1 || $limit > 20) {
$limit = 20;
}

return floor($limit);
}

public function getOffset($page, $limit)
{
$offset = 0;
if ($page != 0 && $page != 1) {
$offset = ($page - 1) * $limit;
}

return $offset;
}
}

Examples


http://football.local/app_test.php/backend/student


{
"students": [
{
"id": 1,
"name": "Robert"
},
{
"id": 2,
"name": "DeNiro"
},
{
"id": 3,
"name": "Inanzzz"
},
{
"id": 4,
"name": "Al"
},
{
"id": 5,
"name": "Pacino"
},
{
"id": 6,
"name": "Inanzzz"
},
{
"id": 7,
"name": "Andy"
},
{
"id": 8,
"name": "Garcia"
},
{
"id": 9,
"name": "Inanzzz"
}
],
"page": 1,
"limit": 20,
"total": "9"
}

http://football.local/app_test.php/backend/student?name=Inanzzz


{
"students": [
{
"id": 3,
"name": "Inanzzz"
},
{
"id": 6,
"name": "Inanzzz"
},
{
"id": 9,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 1,
"limit": 20,
"total": "3"
}

http://football.local/app_test.php/backend/student?name=Inanzzz&limit=2


{
"students": [
{
"id": 3,
"name": "Inanzzz"
},
{
"id": 6,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 1,
"limit": 2,
"total": "3"
}

http://football.local/app_test.php/backend/student?name=Inanzzz&page=2&limit=1


{
"students": [
{
"id": 6,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 2,
"limit": 1,
"total": "3"
}

http://football.local/app_test.php/backend/student?name=nothing


{
"students": [],
"keyword": "nothing",
"page": 1,
"limit": 20,
"total": 0
}