Doctrine query builder içinde limit ve offset kullanarak manual sayfalama yapmak

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
}