31/10/2015 - DOCTRINE, SYMFONY
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.
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 [] []
ID NAME
1 Robert
2 DeNiro
3 Inanzzz
4 Al
5 Pacino
6 Inanzzz
7 Andy
8 Garcia
9 Inanzzz
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:
application_backend.service.student:
class: Application\BackendBundle\Service\StudentService
arguments:
- @application_backend.repository.student
- @application_backend.factory.student
services:
application_backend.repository.student:
class: Application\BackendBundle\Repository\StudentRepository
factory: [@doctrine.orm.entity_manager, getRepository]
arguments: [Application\BackendBundle\Entity\Student]
services:
application_backend.factory.student:
class: Application\BackendBundle\Factory\StudentFactory
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()];
}
}
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);
}
}
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);
}
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);
}
}
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);
}
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;
}
}
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);
}
}
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;
}
namespace Application\BackendBundle\Model\Student;
class Student
{
/**
* @var int
*/
public $id;
/**
* @var string
*/
public $name;
}
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;
}
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;
}
}
{
"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"
}
{
"students": [
{
"id": 3,
"name": "Inanzzz"
},
{
"id": 6,
"name": "Inanzzz"
},
{
"id": 9,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 1,
"limit": 20,
"total": "3"
}
{
"students": [
{
"id": 3,
"name": "Inanzzz"
},
{
"id": 6,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 1,
"limit": 2,
"total": "3"
}
{
"students": [
{
"id": 6,
"name": "Inanzzz"
}
],
"keyword": "Inanzzz",
"page": 2,
"limit": 1,
"total": "3"
}
{
"students": [],
"keyword": "nothing",
"page": 1,
"limit": 20,
"total": 0
}