素書きのSQLで動くCakePHP3のPaginatorComponentを作成してみる

概要

union句の利用があるSQLの場合、CakePHP3のpaginatorが利用できなかったので、素書きのSQLで動くPaginatorComponentのようなComponentを作成することに

要件

以下要件を満たすPaginatorComponentのようなComponentを作成する

• 素書きのSQLを利用できる
• CakePHP3の既存のPaginatorHelperと連携可能
• sort機能は元のPaginationと同様に利用可能

つくったもの

で、つくったやつ。

<?php
namespace App\Controller\Component;

use Cake\Controller\Component;
use Cake\Network\Exception\NotFoundException;
use Cake\Utility\Hash;
use Cake\Datasource\ConnectionManager;

class PaginatorForPdoComponent extends Component
{
    private $_sortColumns = [];

    /**
     * ページのソートに使用するカラム名配列を設定
     *
     * @param $sortColumns sortのカラム名からなる配列
     *
     */
    public function setSortColumns(array $sortColumns)
    {
        $this->_sortColumns = $sortColumns;
    }

    /**
     * ページングしたSQL結果を返す
     *
     * @param $sqlString 実行SQL文字列
     * @param $sqlParams 実行SQLにバインドするパラメータ配列
     * @param $options  Paginator設定値
     *
     * @return array  SQL実行結果
     */
    public function paginateForPdo($sqlString, array $sqlParams, array $options)
    {
        $alias = 'Pager';
        $request = $this->_registry->getController()->request;
        $queryParam = $request->getQueryParams();
        $params = [];
        if (isset($queryParam['sort'])) {
            $params['order'] = $this->_validateSort($queryParam);
        }
        $params['page'] = $queryParam['page'] ?? 1;
        $options = array_merge($options, $params);

        $options += ['page' => 1, 'scope' => null];
        $options['page'] = (int)$options['page'] < 1 ? 1 : (int)$options['page'];
        $limit = (int)$options['limit'];
        $page = $options['page'];

        $countSql = sprintf('select count(*) `count` from (%s) count_table', $sqlString);
        $limitPart = " LIMIT $limit OFFSET " . (($page - 1) * $limit);
        if ($options['order']) {
            $orderPart = ' ORDER BY ' . key($options['order']) . ' ' . current($options['order']);
        } else {
            $orderPart = '';
        }
        $pageSql = $sqlString . $orderPart . $limitPart;
        $results = ConnectionManager::get('default')->execute($pageSql, $sqlParams)->fetchAll('assoc') ?? [];
        $count   = ConnectionManager::get('default')->execute($countSql, $sqlParams)->fetch('assoc')['count'] ?? 0;
        $numResults = count($results);

        $pageCount = (int)ceil($count / $limit);
        $requestedPage = $page;
        $page = max(min($page, $pageCount), 1);

        $order = (array)$options['order'];
        $sortDefault = $directionDefault = false;
        if (!empty($options['default_order']) && count($options['default_order']) == 1) {
            $sortDefault = key($options['default_order']);
            $directionDefault = current($options['default_order']);
        }

        $paging = [
            'finder' => '',
            'page' => $page,
            'current' => $numResults,
            'count' => $count,
            'perPage' => $limit,
            'prevPage' => ($page > 1),
            'nextPage' => ($count > ($page * $limit)),
            'pageCount' => $pageCount,
            'sort' => key($order),
            'direction' => current($order),
            'limit' => null,
            'sortDefault' => $sortDefault,
            'directionDefault' => $directionDefault,
            'scope' => null,
        ];

        if (!$request->getParam('paging')) {
            $request->params['paging'] = [];
        }
        $request->params['paging'] = [$alias => $paging] + (array)$request->getParam('paging');

        if ($requestedPage > $page) {
            throw new NotFoundException();
        }

        return $results;
    }

    private function _validateSort($params)
    {
        $direction  = strtolower($params['direction']) ?? 'asc';
        if (!in_array($direction, ['asc', 'desc'], true)) {
            $direction = 'asc';
        }

        $sortColumn = $params['sort'];
        if (in_array($sortColumn, $this->_sortColumns, true)) {
            return [$sortColumn => $direction];
        }

        return [];
    }

}

PHPとCakePHPのバージョンは以下。

• cakephp3.4
• PHP7.0

これをsrc/Controller/Component配下に追加し、以下のように書くことでとりあえず要件を満たすものが動くことを確認。

class SandboxController extends AppController
{
    public function initialize()
    {
        parent::initialize();
        $this->loadComponent('PaginatorForPdo');
    }

    public function index()
    {
        $options = [
            'limit' => 10,
            'order' => [
                'id' => 'desc'
            ]
        ];
        $this->PaginatorForPdo->setSortColumns(['id', 'tname']);
        $this->set('items',
            $this->PaginatorForPdo->paginateForPdo('select s.id, s.tname from sandbox s where id > ?', [20], $options)
        );
    }
}

　PaginatorComponentとの違い

• ソートカラムにtable1.name, table2.nameというようなテーブル名の指定不可
  以下のようにselect句にaliasをつけ、table1_name, table2_nameとすることで代替可

select table1.name table1_name, table2.name table2_name from ...

• setSortColumns()関数を追加
  設定している値はPaginatorComponentのオプションであるwhitelistにあたる値

• パラメータがSQL文

• 関数名がpaginateForPdo。※変えた理由はとくにない。

作成までの紆余曲折

まずは

とりあえず、データ取得のPaginatorComponentのソースコードを眺め、viewに渡しているデータを確認
※コメントは私が説明のために記載

$paging = [
    'finder' => $finder,    // 1).Modelのfinder名（データ取得の使用条件）
    'page' => $page, // 2).現在のページ番号
    'current' => $numResults, // 3).現在の表示件数
    'count' => $count,  // 4).全体件数
    'perPage' => $limit, // 5).1ページあたりに表示する件数
    'prevPage' => ($page > 1), // 6).前ページ存在有無
    'nextPage' => ($count > ($page * $limit)), // 7).次ページ存在有無
    'pageCount' => $pageCount,  // 8).全体ページ件数
    'sort' => key($order),  // 9).ソートカラム
    'direction' => current($order), // 10).ソート方向（asc:昇順 or desc:降順）
    'limit' => $defaults['limit'] != $limit ? $limit : null,　// 11).limit設定有無?
    'sortDefault' => $sortDefault, // 12).初期表示時のソートカラム
    'directionDefault' => $directionDefault, // 13).初期表示時のソート方向
    'scope' => $options['scope'],  // 14).複数Model利用時に使用するオプション値
];

if (!$request->getParam('paging')) {
    $request->params['paging'] = [];
}
// view変数に設定。15).$aliasは一般的にはmodel名が入る様子
$request->params['paging'] = [$alias => $paging] + (array)$request->getParam('paging');

if ($requestedPage > $page) {
    throw new NotFoundException();
}

// resultsにはviewに表示するデータ
return $results;

1).のfinderと14).のscope
これはmodelでデータ取得に使用する値なので、SQL文直書きなら使用しないので、エラーが発生しない適当な値として、空文字とnullを設定

15).の$alias
PaginatorHelperを眺めてみたところ、データ取得に結合(joinの使用）があった場合で以下のようなリンク出力の[Model名].[カラム名]の生成に使っていた。

<a href="/example.co.jp?sort=[Model名].[カラム名]&direction=asc

ソートカラムをtable1.name, table2.nameとしなければ別段問題ないようなのでこれも適当な値として"Paginator"と設定

次に

他の処理を眺めてみる
※コメントは私が説明のために記載

// 0).データ取得オブジェクト指定
if ($object instanceof QueryInterface) {
    $query = $object;
    $object = $query->repository();
}

// 1).パラメータの整合性チェックと2)のデータ取得で使用するパラメータ設定
$alias = $object->alias();

$options = $this->mergeOptions($alias, $settings);
$options = $this->validateSort($object, $options);
$options = $this->checkLimit($options);

$options += ['page' => 1, 'scope' => null];
$options['page'] = (int)$options['page'] < 1 ? 1 : (int)$options['page'];
list($finder, $options) = $this->_extractFinder($options);

// 2).データ取得
/* @var \Cake\Datasource\RepositoryInterface $object */
if (empty($query)) {
    $query = $object->find($finder, $options);
} else {
    $query->applyOptions($options);
}
$results = $query->all();

// 3).viewに渡す各データ取得
$numResults = count($results);
$count = $numResults ? $query->count() : 0;

$defaults = $this->getDefaults($alias, $settings);
unset($defaults[0]);

$page = $options['page'];
$limit = $options['limit'];
$pageCount = (int)ceil($count / $limit);
$requestedPage = $page;
$page = max(min($page, $pageCount), 1);
$request = $this->_registry->getController()->request;

$order = (array)$options['order'];
$sortDefault = $directionDefault = false;
if (!empty($defaults['order']) && count($defaults['order']) == 1) {
    $sortDefault = key($defaults['order']);
    $directionDefault = current($defaults['order']);
}

0).データ取得オブジェクト指定
これはSQL文直書きであるため、不要

1).パラメータの整合性チェックと2).のデータ取得で使用するパラメータ設定
order句にしているカラム名、limit句にしている数値はバインドできずまた、クライアント側からくる値なので、SQLインジェクション対策として、validate処理を実施している。
また、データ取得に使用するoffset値、limit値、order句の値もあわせて取得している。
→元処理を参考に適当に実装

2).データ取得
1).で取得したoffset値、limit値、order句の値からSQL文を作成し、実行結果を設定する処理で実装

3).viewに渡す各データ取得
上記の 4).全体件数 $countの取得処理のみ対応し、他そのまま。
※全体件数取得SQLは以下のようにして生成

select count(*) `count` from ([SQL文]) count_table