はじめに

初めまして、10月にｋ．ｓ．ロジャースに入社した西谷です。
今までWindows系の開発をしており、Web系に転職して2週間と右も左も分からない状態です。
そんな中で早速ですが、仕事上で躓いた点を共有できたらと思います。
また、間違い等があればコメントにてお知らせいただけたらと思います。

構成

弊社ではPhalconPHP、APIサーバであれGolang、フロントエンドはReactを中心に開発を進めています。
今回は長年続いているプロジェクトで使っているFuelPHPの話をしたいと思います。
ただし、DB周りは可能な限りORMを使うスタイルで開発しています。

FuelPHP
MySQL

また、以降記載しているSQLは可読性のため、直接関係しない部分の削除等の編集をしています。

Paginationについて

Webシステムを作る上で、ページネーションはぼほ出現する機能かと思います。

こんなやつです

フレームワークで用意されているため、深く考えずに簡単に実装することが出来ます。

// DBからデータ取得
$query = Model_Hoge::query()->related(['Model_A', 'Model_B']);

// pagination作成
$config = [
   'uri_segment' => 'page',
   'per_page' => 20,
   'num_links' => 5,
   'total_items' => $query->count(),
   'name' => 'hogehoge',
];
$pagination = Pagination::forge('mypagination', $config);

// 実際に表示するデータ
$data = $query->order_by('id', 'desc')
           ->rows_limit($pagination->per_page)
           ->rows_offset($pagination->offset)
           ->get();
// View生成
$view = View::forge('hogehoge', $data);

<div class="pagination">
   <?php echo Pagination::instance('mypagination')->render(); ?>
</div>

起きた問題

Paginationの中身を深く理解せずに利用していたため、次のようなバグが発生しました。

ページ内に表示されないデータがある
絞り込みをすると、データがあるのに何も表示されない

結論から書くと、Paginationに使うクエリのhas_many, many_to_manyのリレーションを含むとバグが発生する可能性があります。
バグの発生原因と回避法について検討していきたいと思います。

原因

１ページに表示する内容を取得する場合は

$data = $query->order_by('id', 'desc')
           ->rows_limit($pagination->per_page)
           ->rows_offset($pagination->offset)
           ->get();

のようなコードを書くと思います。

つまり、以下のSQLが基本形となります。

SELECT *
FROM Table1
LIMIT [表示数] OFFSET [現在のページ × 表示数]

これが、リレーションを含むとlimit/rows_limit周りでややこしいことが起きます。

rows_limitを利用する場合

当初はrows_limit, rows_offsetを利用していました。
次のようなコードを書いた場合に該当します。

// 実際に表示するデータ
$data = $query->order_by('id', 'desc')
           ->rows_limit($pagination->per_page)
           ->rows_offset($pagination->offset)
           ->get();

このクエリにリレーションが含まれている場合、次の様なSQLが生成されます。

SELECT *
FROM TableA AS `t0`
LEFT JOIN TableB AS `t1` ON `t0`.key = `t1`.key
LIMIT 3 OFFSET 0

このとき、TableAとTableBが1対1の関係であれば、問題は起きません。
しかし、1対多ではどうでしょうか？

rows_limit, rows_offsetを除けば、次のようなレコード得られることが想像できます。

TableA.key	TableA.value	TableB.key	TableB.value
1	A1	1	B1
1	A1	2	B2
1	A1	3	B3
2	A2	1	B1
2	A2	2	B2
2	A2	3	B3
3	A3	1	B1
3	A3	2	B2
3	A3	3	B3

ここが問題で、LIMITの位置を確認しますと、最後の結果に対して制限を掛けています。
つまり、1ページに3件のデータを表示することを期待してper_page = 3と設定すると、以下の部分のみ取得することになります。

TableA.key	TableA.value	TableB.key	TableB.value
1	A1	1	B1
1	A1	2	B2
1	A1	3	B3

これでは当然、画面に1件のデータのみが表示されてしまいます。

limitを利用する場合

次にlimitについて試しました。

// 実際に表示するデータ
$data = $query->order_by('id', 'desc')
           ->limit($pagination->per_page)
           ->offset($pagination->offset)
           ->get();

生成SQLは次のようになります。

SELECT *
FROM (
   SELECT * FROM TableA LIMIT 4 OFFSET 0
) AS `t0`
LEFT JOIN TableB AS `t1` ON `t0`.key = `t1`.key
WHERE `t0`.hoge = 'hoge'

サブクエリにLIMITが設定されるため、上手くいきそうですが、別の問題が発生しました。
TableB.valueの絞り込みをしようとした場合、動作しません。
理由はシンプルで、サブクエリが1ページ目でLIMITされているため、2ページ目以降のデータが存在しないためです。

1ページ目を表示した状態でTableB.valueのB4を検索すると、WHERE文が実行されるタイミングではデータが存在しないことになります。

(Page)	TableA.key	TableA.value	TableB.key	TableB.value
1	1	hoge	1	B1
1	1	hoge	2	B2
1	2	hoge	2	B2
1	2	hoge	3	B3
2	3	fuga	3	B3
2	3	fuga	4	B4

回避方法

残念ながら力及ばず、結合部分を切り出して対応しました。

1対多/多対多のTableに対して絞り込みを行い、idを取得
マスタテーブルに対してidで絞り込み
Paginationのlimitを掛けた結果、idを再度取得
必要テーブル全て結合したクエリに対して、idで検索

// 結合テーブルに対する絞り込み
$refine_query = Model_TableA::query()->related(['TableB'])
           ->where('TableB.foo', 'bar')
           ->get();

// 条件に一致するIDを取り出す
$id = [];
foreach ($refine_query as $rows) {
   $id[] = $rows["id"];
}
if (empty($id)) return;

// マスタテーブルに対して、idで検索後にlimitをかける
$query = Model_TableA::query()->where('id', 'in', $id);
$query->order_by('id', 'asc')
   ->rows_limit($pagination->per_page)
   ->rows_offset($pagination->offset)
   ->get();

// 1ページ内に表示する内容のID
$id = [];
foreach ($query as $rows) {
   $id[] = $rows["id"];
}

// 表示する内容
$content = Model_TableA::query()->related(['TableB', 'TableC' ...])
   ->where('id', 'in', $id)
   ->get();

終わりに

今回はFuelPHPのPaginationについてご紹介させて頂きました。
泥臭い回避法がになってしまいましたが、もっとスマートな解決方法をご存知の方は是非コメントにて教えて頂けたらと思います！

Wantedlyでもブログ投稿してます

Techブログに加えて会社ブログなどもやっているので、気になった方はぜひ覗いてみてください。
https://www.wantedly.com/companies/ks-rogers