京都のシステム開発・ホームページ制作会社　株式会社Nextat（ネクスタット）

こんにちは、スズキです。

Laravel先生にはページネーションという便利な機能があります。
このページネーションですが、以前、「ページネーションの基準を親テーブルにしつつ、取得するデータを子テーブル基準にすることってできないかな？」と試行錯誤をしたことがあったのでその際に行った対応方法を紹介しようと思います。


「ページネーションの基準を親テーブルにしつつ、取得するデータを子テーブル基準にする」って言い方だとわかりくにくいかもしれないですが、
ソースで表すと以下のような構造を指しています。

{{ $parentTable->total() }}件中 {{ $parentTable->firstItem() }}～{{ $parentTable->lastItem() }}件 表示
<br>
{!! $parentTable->appends(\Input::all())->render() !!}
<br>
<table>
    <tr>
        <th>親名</th>
        <td>{{ $parentTable->name }}</td>
    </tr>
    <tr>
        <th>子情報</th>
        <td>
            <ul>
                @foreach($parentTable->child_tables as $childTable)
                    <li>
                        子名：{{ $childTable->name }}
                    </li>
                @endforeach
            </ul>
        </td>
    </tr>
</table>

 
上記のように
「合計何件」「何ページ目」という部分は親テーブルを基準にした情報を表示し、
表示する情報は「検索条件を満たした子テーブル情報のみ」を表示する、
というのが行いたいことになります。

さて、この場合、何が問題になるかというと、Laravelを利用する場合はN+1問題を回避するために、以下のような方法でデータを取得する必要がある、ということです。

        $query = ParentTable::with([
            'child_tables',
        ]);
        $query->select([
            'parent_tables.*',
        ]);
        $perPage = 10;
        $rows = $query->paginate($perPage);

上記を見るとわかるように、Laravelの機能をそのまま利用するだけだと「親テーブルに紐づく子テーブル情報が全て取得されてしまう」という問題があるわけです。
そのため、「検索条件を満たした子テーブル情報のみを取得するようにしたい」となった場合にはちょっとした工夫が必要になります。

ではどうやって実現しようか、という話になるわけですが、私が以前対応した際には、MySQLにGROUP_CONCAT関数という都合の良い関数があったのでこれを利用することにしました。
GROUP_CONCAT関数は「グループ内の各値をカンマ区切りで連結した文字列を取得する」という内容の関数です。

例えば、

SELECT
  test_tables.id
FROM
  test_tables
;

の取得結果が「1」「2」「3」という３つのレコード値だとしたら

SELECT
  GROUP_CONCAT(test_tables.id)
FROM
  test_tables
;

の取得結果は「1,2,3」という文字列になります。 


GROUP_CONCAT関数の存在さえ知ってしまえば、後は簡単。
最終的には以下のようなコードを記述することで目的の挙動を実現することができました。

    /**
     * 検索処理を行います。
     * @return \Illuminate\Contracts\Pagination\LengthAwarePaginator
     */
    private function search()
    {
        // サブクエリ情報作成
        $subQuery = \DB::table('parent_tables');
        $subQuery->select([
            'parent_tables.id',
            \DB::raw('GROUP_CONCAT(child_tables.id) AS concat_child_id_list'),
        ]);
        $subQuery->join('child_tables', 'parent_tables.id', '=', 'child_tables.parent_id');
        $subQuery->groupBy('parent_tables.id');

        // 検索条件はサブクエリ側に対して適用する
        $subQuery->whereRaw('parent_tables.publish_flg = 1');
        $subQuery->whereRaw('child_tables.publish_flg = 1');
        $subQuery->whereRaw('child_tables.price < 10000');

        // メインクエリ情報作成
        $query = ParentTable::with([
            'child_tables',
        ]);
        $query->select([
            'parent_tables.*',
            // 該当した子テーブルのID情報を取得する
            'sub_parent_tables.concat_child_id_list',
        ]);
        // サブクエリ情報の内、取得に成功した親テーブルのみを取得対象にする
        $query->join("({$subQuery->toSql()}) AS sub_parent_tables", 'parent_tables.id', '=', 'sub_parent_tables.id');

        // データ取得
        $perPage = 10;
        $searchResult = $query->paginate($perPage);


        // 取得結果から検索条件を満たした子テーブルのみが残るようにデータを加工する
        foreach($searchResult as $key => $parentTable)
        {
            // 検索条件を満たした子テーブルのID値のリストを取得する
            if (!strlen($parentTable->concat_child_id_list)) {
                continue;
            }
            $childIdList = explode(",", $parentTable->concat_child_id_list);

            // 検索条件を満たした子テーブル情報のリストを取得する
            $retChildTables = [];
            foreach($parentTable->child_tables as $childTable)
            {
                // 対象の子テーブル情報以外は無視
                if (!in_array($childTable->id, $childIdList)) {
                    continue;
                }

                // 戻り値情報に追加
                $retChildTables[] = $childTable;
            }

            // 子テーブル情報更新
            $searchResult[$key]->child_tables = $retChildTables;
        }

        return $searchResult;
    }

一応、ID値を結合した文字列を取得する関係で、子テーブルの数が多すぎる場合はMySQLの文字数上限に引っかかって正常に動作しなくなる可能性があるので注意してください。