Tableクラスに定義可能なhasManyアソシエーションは、conditionsにて抽出条件を書くことが可能だ。普通はjoin先テーブルの特定のカラムの値で絞り込むとか単純なことをさせるのに使うが、たまたま
hasMany()でjoinさせたいテーブルの中で、特定のカラムが最大値のレコードだけをデフォルトで抽出したい
というニーズがあったので、これをどうにかしてhasMany()を定義する際に書けないかと思い調べてみたら方法があったのでシェアする。検証したCakePHPのバージョンは3.6.13。
お題として取り扱うデータベースの解説
とある駐車場内に停車している車を、毎時で記録する監視システムがあったとしよう。テーブルリレーションはこんな感じ：
各テーブルのレコードはこんな感じ：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 |
mysql> select * from parkings; +----+------------+---------------------+---------------------+ | id | name | created | modified | +----+------------+---------------------+---------------------+ | 1 | A駐車場 | 2020-06-07 17:50:53 | 2020-06-07 17:50:53 | | 2 | B駐車場 | 2020-06-07 17:51:17 | 2020-06-07 17:51:17 | | 3 | C駐車場 | 2020-06-07 17:51:47 | 2020-06-07 17:51:47 | +----+------------+---------------------+---------------------+ 3 rows in set (0.00 sec) mysql> select * from master_cars; +----+--------------------------------+ | id | name | +----+--------------------------------+ | 1 | トヨタ スープラ | | 2 | マツダ MAZDA6セダン | | 3 | マツダ MAZDA6ワゴン | | 4 | マツダ ロードスター | | 5 | マツダ ロードスターRF | | 6 | マツダ CX-5 | +----+--------------------------------+ 6 rows in set (0.01 sec) mysql> select * from parking_cars; +----+------------+--------+---------------------+---------------------+---------------------+ | id | parking_id | car_id | target_datetime | created | modified | +----+------------+--------+---------------------+---------------------+---------------------+ | 1 | 1 | 1 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 2 | 1 | 2 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 3 | 1 | 3 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 4 | 1 | 4 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 5 | 1 | 1 | 2020-06-01 09:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 6 | 1 | 2 | 2020-06-01 09:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 7 | 1 | 1 | 2020-06-01 10:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 8 | 1 | 4 | 2020-06-01 18:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 9 | 1 | 1 | 2020-06-01 19:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 10 | 1 | 4 | 2020-06-01 19:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 11 | 1 | 1 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 12 | 1 | 2 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 13 | 1 | 3 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 14 | 1 | 4 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 15 | 2 | 1 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 16 | 2 | 2 | 2020-06-01 08:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 17 | 2 | 1 | 2020-06-01 09:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | +----+------------+--------+---------------------+---------------------+---------------------+ 17 rows in set (0.01 sec) mysql> |
parking_carsでは、各駐車場に毎時停車している車が記録されている。この中から、各駐車場に停車している最新の車情報だけを常に収集できるCakePHP3アプリケーションを作りたいとする。
発行したいSQLの解説
ずばりこんな感じ：
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT ParkingCar.* FROM parkings Parking INNER JOIN parking_cars ParkingCar ON ParkingCar.parking_id = Parking.id LEFT JOIN parking_cars TmpParkingCar ON TmpParkingCar.parking_id = ParkingCar.parking_id AND TmpParkingCar.target_datetime > ParkingCar.target_datetime WHERE TmpParkingCar.target_datetime IS NULL ; |
結果はこうなる：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
mysql> SELECT -> ParkingCar.* -> FROM -> parkings Parking -> INNER JOIN parking_cars ParkingCar -> ON ParkingCar.parking_id = Parking.id -> LEFT JOIN parking_cars TmpParkingCar -> ON TmpParkingCar.parking_id = ParkingCar.parking_id -> AND TmpParkingCar.target_datetime > ParkingCar.target_datetime -> WHERE -> TmpParkingCar.target_datetime IS NULL -> ; +----+------------+--------+---------------------+---------------------+---------------------+ | id | parking_id | car_id | target_datetime | created | modified | +----+------------+--------+---------------------+---------------------+---------------------+ | 11 | 1 | 1 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 12 | 1 | 2 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 13 | 1 | 3 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 14 | 1 | 4 | 2020-06-01 20:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | | 17 | 2 | 1 | 2020-06-01 09:00:00 | 2020-06-07 18:47:34 | 2020-06-07 18:47:34 | +----+------------+--------+---------------------+---------------------+---------------------+ 5 rows in set (0.00 sec) mysql> |
各駐車場に停車している最新の車情報が取得できている。
実際のhasMany()実装事例
これをCakePHP3のhasMany()で表現してみよう。各駐車場に停車している最新の車情報を知りたい場合はこのような書き方が可能だ：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
<?php namespace App\Model\Table; use Cake\ORM\Query; use Cake\ORM\RulesChecker; use Cake\ORM\Table; use Cake\Validation\Validator; class ParkingsTable extends Table { public function initialize(array $config) { parent::initialize($config); $this->setTable('parkings'); $this->setDisplayField('id'); $this->setPrimaryKey('id'); // target_datetimeが最新のparking_carsだけをjoin $this->hasMany('ParkingCars', [ 'foreignKey' => 'parking_id', 'conditions' => function ($exp, $query) { $query->join([ 'TmpParkingCars' => [ 'table' => 'parking_cars', 'type' => 'LEFT', 'conditions' => [ 'TmpParkingCars.parking_id = ParkingCars.parking_id', 'TmpParkingCars.target_datetime > ParkingCars.target_datetime', ], ], ]); return $exp->add(['TmpParkingCars.target_datetime IS' => null]); }, ]); } } |
このように書いておけば、parkingsにcontainでparking_carsをjoinした際に、常に最新のparking_carsの情報だけを収集してくれるようになる。hasManyのconditionsに複雑なことをゴニョゴニョ書きたくない場合はfinderというキーを使って、join先テーブルにロジックを逃すのも手だ。
最大値を持つレコードだけ抽出するSQLは他にもwhere existsを使う方法もあると思うので、SQLパフォーマンスに気をつけながらトライしてみていただきたい。
参考）