はじめに

CakePHP4を実務で仕様した際、タイトルの内容の対処法がわからず困ったので、備忘録としてこの記事に記載したいと思います。
タイトルの内容以外にも、CakePHP4のデータの結合に関してまとめておりますので、CakePHPを実務で利用している方、特に初心者の方で公式ドキュメントを読んだだけでは理解が難しいと思った方向けの記事になりますのでよろしくお願いします。

ER図

私が書いた下記記事と同じものになります。
CakaPHP4でユーザ追加時に関連するテーブルも同時に追加を行う方法

今回は、ユーザの一覧を表示する際に、特定の委員会に絞って抽出した場合の説明を行います。
ER図のリレーションの関係上、belongsToManyの関係になってしまっていますが、
hasManyでも同じことができます。hasManyのアソシエーションで困っている方もご活用ください。

結論

UsersController.php

    public function index()
    {
        $users = $this->Users->find()
            ->matching('Committees', function ($q) {
                // ここに条件を記載
                return $q->where(['Committees.id' => 1]);
            });
        $users = $this->paginate($users);
        $this->set(compact('users'));
    }

UsersTable.php

    public function initialize(array $config): void
    {
        $this->belongsToMany('Committees', [
            // 規則に則ったテーブル名ではないので、中間テーブルの名称を記載
            'joinTable' => 'affiliation_committees',
        ]);
    }

UsersTable.php にアソシエーションを記載します。
中間テーブルの名称が規則に則ったものではないので、中間テーブルの名称をオプションとして記載しています。

UsersController.phpではmatchingという記載を行い結合します。
その上で、無名関数を使うことで結合したテーブルに対し条件の記載を行います。
matchingを使えば後で結合した側のテーブルの情報（本記事の場合はCommittees）も
利用することができます。ですので、今回の場合、画面に委員会を出力したいので、

index.php

<?php foreach ($users as $user) : ?>
    <tr>
        <td><?= $this->Number->format($user->id) ?></td>
        <td><?= h($user->user_name) ?></td>
        <td><?= h($user->email) ?></td>
        <td><?= h($user->created) ?></td>
        <td><?= h($user->modified) ?></td>
        <!-- 下記内容で画面側に出力 -->
        <td><?= h($user->_matchingData['Committees']->committee_name) ?></td>

        <td class="actions">
            <?= $this->Html->link(__('View'), ['action' => 'view', $user->id]) ?>
            <?= $this->Html->link(__('Edit'), ['action' => 'edit', $user->id]) ?>
            <?= $this->Form->postLink(__('Delete'), ['action' => 'delete', $user->id], ['confirm' => __('Are you sure you want to delete # {0}?', $user->id)]) ?>
        </td>
    </tr>
<?php endforeach; ?>

と記載してください。

発行されているSQL

今回の処理で発行されたSQLはというと、

SELECT 
  Users.id AS Users__id, 
  Users.user_name AS Users__user_name, 
  Users.email AS Users__email, 
  Users.password AS Users__password, 
  Users.department_id AS Users__department_id, 
  Users.created AS Users__created, 
  Users.modified AS Users__modified 
FROM 
  users Users 
  INNER JOIN affiliation_committees AffiliationCommittees ON Users.id = AffiliationCommittees.user_id 
  INNER JOIN committees Committees ON (
    /* 条件が反映されている箇所 */
    Committees.id = 1 
    AND Committees.id = AffiliationCommittees.committee_id
  )

無名関数で記載した部分がON句に記載されています。

よくやってしまう勘違い

私が最初にしてしまった記載方法は

UsersController.php

    public function index()
    {
        $users = $this->Users->find('all', ['contain' => ['Committees']])
            // whereで条件を記載していた
            ->where(['Committees.id' => 1]);
        $users = $this->paginate($users);
        $this->set(compact('users'));
    }

やりたいことから逆算して公式ドキュメントを確認していると、クエリビルダーという章もあり、
このような記載をして、カラムがないというエラーになっていました。
厄介なのが、比較的すぐ見つかる記法がこのcontainを使う方法であるということ、
そして一番厄介なのは、belongsToのアソシエーションの場合エラーにならないことだと思います。

私のER図にはdepartmentsというテーブルがあり、usersからすると、belongsToのアソシエーションです。
なので、

UsersController.php

    public function index()
    {
        $users = $this->Users->find('all', ['contain' => ['Departments']])
            ->where(['Departments.id' => 1]);
        $users = $this->paginate($users);
        $this->set(compact('users'));
        $users = $this->paginate($users);
        $this->set(compact('users'));
    }

上記ではエラーになりません。SQLとしても、

SELECT 
  Users.id AS Users__id, 
  Users.user_name AS Users__user_name, 
  Users.email AS Users__email, 
  Users.password AS Users__password, 
  Users.department_id AS Users__department_id, 
  Users.created AS Users__created, 
  Users.modified AS Users__modified, 
  Departments.id AS Departments__id, 
  Departments.department_name AS Departments__department_name, 
  Departments.created AS Departments__created, 
  Departments.modified AS Departments__modified 
FROM 
  users Users 
  INNER JOIN departments Departments ON Departments.id = Users.department_id 
WHERE 
  Departments.id = 1

思った通りの形になっています。「それと同じことをCommitteesでもやりたいだけなんだけど…」とイライラした記憶が蘇ってきました笑

原因

belongsToManyのアソシエーションの場合発行されているSQL

実は、belongsToManyのアソシエーションの場合、発行されているSQLは1つではありません。

ユーザ情報の取得

SELECT 
  Users.id AS Users__id, 
  Users.user_name AS Users__user_name, 
  Users.email AS Users__email, 
  Users.password AS Users__password, 
  Users.department_id AS Users__department_id, 
  Users.created AS Users__created, 
  Users.modified AS Users__modified 
FROM 
  users Users

委員会情報の取得

SELECT 
  AffiliationCommittees.id AS Committees_CJoin__id, 
  AffiliationCommittees.user_id AS Committees_CJoin__user_id, 
  AffiliationCommittees.committee_id AS Committees_CJoin__committee_id, 
  AffiliationCommittees.role_id AS Committees_CJoin__role_id, 
  AffiliationCommittees.created AS Committees_CJoin__created, 
  AffiliationCommittees.modified AS Committees_CJoin__modified, 
  Committees.id AS Committees__id, 
  Committees.committee_name AS Committees__committee_name, 
  Committees.created AS Committees__created, 
  Committees.modified AS Committees__modified 
FROM 
  committees Committees 
  INNER JOIN affiliation_committees AffiliationCommittees ON Committees.id = AffiliationCommittees.committee_id 
WHERE 
                                    /* 条件に入っているのはユーザのID */
  AffiliationCommittees.user_id in (11, 12, 13)

SQLとしては2つ発行されています。

なぜそうなるのか

CakePHPのEagerローディングという仕様のためです。

Eager ローディング
できるだけ少ないクエリーでDBから情報を取得できるようにJOINを（可能なときは）使います。 HasMany アソシエーションを使うような分割したクエリーが必要なときは、1つのクエリーで、現在のオブジェクト一式に必要な全ての関連データを取ってこようとします。

Lazy ローディング
絶対に必要になるまでアソシエーションのロードを遅延させます。これにより、不要なデータがオブジェクト化されないので CPU 時間を節約できますが、大量のクエリーがDBに送られることになるかもしれません。例えば、複数の記事 (articles) とそれに属するコメント (comments) を舐めるループでは、イテレートされた記事の数だけクエリーが何度も送られることになります。

ここだけ読んでもなんのこっちゃという感じかもしれませんが、要はN+1問題が起きないようにするためですね。
N+1問題についてはググってもらえれば出てくるかと思うのでこの記事には記載しませんが、WEBアプリケーションを開発するなら必須知識かと思います。

終わりに

公式ドキュメントにはシンプルなデータの取得の方法は書かれてあるのですが、
実務にあわせて条件を追加するというのはエラーがなかなか解決せず、
苦労しました。同じようなことで困っている人がいてお役に立てれば幸いです。