Eğer sayfalama işlemleri için KnpPaginatorBundle veya Doctrine Pagination kullanıyorsanız, doctrine query log içinde aşağıdakine benzer bir göz yaşartıcı query ile karşı karşıya kalabilirsiniz. Bu sadece toplam kaydı bulmak için çalıştırılıyor. Aşağıdaki query örneği her ne kadar kısaltılmış bir versiyon olsa bile, yapacağınız işleme göre ve veritabanındaki toplam kayıt sayısına göre, query daha da büyüyebilir. Bu iki önemli önemli probleme yol açar. Birincisi, query yavaş çalışır. İkincisi ise, zaman zaman 500 Internal Server Error hatası alabilirsiniz. Benim başıma geldi. Sorunu çözmek için, aşağıdaki manual sayfalama örneğini kullanabilirsiniz.


Göz yaşartıcı 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 [] []

Test veriler


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;
}
}

Örnekler


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
}