Doctrine と PostgreSQL でページングをする

この記事は、Symfony, Doctrine, PostgreSQL で Web アプリを開発している時に、ページング処理をどうするか。という記事です。
いろいろ調べて解決方法を見つけましたが、Doctrine の Pagenation を使うとよい。という結論になりました。しかし、途中のソリューションもなかなか面白いので記事にしてみました。

Symfony のバージョンは Symfony4 または Symfony5 を想定しています。

PostgreSQL での総件数の取得

ページングする場合、PostgreSQL の SQL では MySQL と同じように offset と limit を使います。

SELECT * FROM my_table LIMIT 20 OFFSET 0;

MySQL では SQL_CALC_FOUND_ROWS と FOUND_ROWS() を使って全件数を取得できますが、PostgreSQL の場合は over() 関数を count() と組み合わせて用います。

SELECT *, count(*) OVER() total FROM my_table LIMIT 20 OFFSET 0;

Doctrine での offset, limit の指定方法

Doctrine の native query を使っても良いのですが、QueryBuilder や DQL で、やりたいところです。
Doctrine で offset, limit を指定するには Query の setMaxResults() と setFirstResult() を使います。

$dql = "SELECT t FROM App\Entity\MyTable t";
$query = $this->em->createQuery($dql);
$query->setMaxResults(20)   // limit
    ->setFirstResult(0);    // offset
$result = $query->getResult();

これでページングはできるのですが、総件数を取得することはできません。
そこで、最初に紹介した count() と over() を組み合わせて取得する方法を、Doctrine のカスタム関数で実現します。

Doctrine カスタム関数の定義

src/DQL/CountOver.php

<?php
/** @noinspection PhpUnused */

namespace App\DQL;


use Doctrine\ORM\Query\AST\Functions\FunctionNode;
use Doctrine\ORM\Query\Lexer;
use Doctrine\ORM\Query\Parser;
use Doctrine\ORM\Query\QueryException;
use Doctrine\ORM\Query\SqlWalker;

/**
 * Class CountOver
 * @package App\DQL
 */
class CountOver extends FunctionNode
{
    /**
     * @inheritDoc
     */
    public function getSql(SqlWalker $sqlWalker)
    {
        return "COUNT(*) OVER()";
    }

    /**
     * @inheritDoc
     * @throws QueryException
     */
    public function parse(Parser $parser): void
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}

上記のクラスを用意します。
このクラスが Symfony の autowire に引っかからないように、services.yaml で除外しておきましょう。

config/services.yaml

services:
    App\:
        resource: '../src/*'
        exclude:
            - '../src/{DependencyInjection,Entity,Migrations,Tests,Kernel.php}'
            - '../src/DQL'

Doctrine にカスタム関数を追加します。

doctrine:
    orm:
        dql:
            string_functions:
                count_over: App\DQL\CountOver

以上の設定で、count_over() 関数が DQL の中で使えるようになります。

DQL からの利用

定義した関数は次のように使います。

$dql = "SELECT t, count_over() as total FROM App\Entity\MyTable t";
$query = $this->em->createQuery($dql);
$query->setMaxResults(20)   // limit
    ->setFirstResult(0);    // offset
$result = $query->getResult();
$total = $result[0]['total'];   // total を取得

または QueryBuilder で

$qb = $em->createQueryBuilder();
$qb->select('count_over() as total');

でも、こういうのって ORM に用意されていないのかな？
されているよ。ということで次へ。

Pagenator を利用する

Doctrine の Pagination を使うのが正しい方法です。

$dql = "SELECT t FROM App\Entity\MyTable t";
$query = $this->em->createQuery($dql);
$query->setMaxResults(20)   // limit
    ->setFirstResult(0);    // offset
$paginator = new Paginator($query);
$total = count($paginator); // total を取得
$result = $paginator->getQuery()->getResult();

これで、log を見てみますと、SQL クエリが 2回投げられています。
総件数を取得するためのクエリと、データを取得するクエリです。

やっぱり、1回のクエリで総件数を取得したい！という時には前半部分の解決方法を採用するのもいいかもしれません。