hasMany のテーブルデータを、条件指定で1レコードのみ hasOne で取得したい
hasMany
のテーブルデータを、条件指定で1レコードのみ hasOne
で取ってきたい。
例えば、次のような機能があるとする。
記事が投稿できる。
記事は即公開もできるし、公開予定日時も指定できる。
公開予定日時が存在する時はそれをもとに記事が公開される、という感じ。
公開予約日時は記事に紐づけて登録する。
記事の一覧画面では、登録されている記事の情報とともに、公開予約されている場合は、それも一覧に表示したい。
記事のID,タイトル等でソートできるのと同様、公開予約日時でもソートできるようにしたい。
環境や仕様
CakePHP v3.2.13
DBテーブル
各テーブルは以下のようになっている前提。
- 記事テーブル(1)
Column | PK | 備考 |
---|---|---|
記事_id | ● | |
タイトル | ||
本文 | ||
公開日時 | 年月日時分秒 | |
更新日時 | 年月日時分秒 |
- 記事公開予約日時テーブル(多)※複合主キー
Column | PK | 備考 |
---|---|---|
記事_id | ● | |
公開予約日時 | ● | 年月日時分 |
更新日時 | 年月日時分秒 |
- 仕様
- 記事公開予約日時は 変更できる
- 年月日時分指定(秒は指定しない)
- 記事公開予約日時を変更する時は、現在時刻<公開予定日時 のレコードが存在する場合は一律削除されて、新しい更新後の公開予定日時レコードが登録される
- 公開予定日時を過ぎたものは、そのままテーブルに履歴として残る
- テーブルには、最新の予約日時と、過去の予約日時のレコードが混在する
- 記事公開予約日時は 変更できる
hasOneへの道
まずは、普通に hasMany
指定
普通に考えれば、Model/Tableクラスは「1対多」なので、 hasMany
指定する。
記事テーブルに hasMany
でassociation設定して、
Controller側では
$this->記事->find()->contain('記事公開予約日時');
で、取ってくるイメージ。
#/Model/Table/記事Table.php $this->hasMany('記事公開予約日時', [ 'className' => '記事公開予約日時', 'foreignKey' => '記事_id', ]);
hasManyの時の、CakePHPの動き
hasMany
でcontainしてデータ取ってくると、hasMany
のデータはcontain元のデータに配列でくっついてくる。
(int) 0 => object(App\Model\Entity\記事) { 'id' => (int) 10000001, 'title' => '記事タイトル', 'program_text' => '記事本文', '公開日時' => object(Cake\I18n\Time) , '更新日時' => object(Cake\I18n\Time) , '記事公開予約日時' => [ (int) 0 => object(App\Model\Entity\記事公開予約日時) { '記事_id' => (int) 10000001, '公開予約日時' => object(Cake\I18n\Time) { '更新日時' => object(Cake\I18n\Time) { }, (int) 1 => object(App\Model\Entity\記事公開予約日時) { '記事_id' => (int) 10000001, '公開予約日時' => object(Cake\I18n\Time) { '更新日時' => object(Cake\I18n\Time) { }, : : ],
hasMany
を指定するとデータ取得時に、元のテーブル(ここでは記事テーブル)を検索するQueryと、記事公開予約日時テーブルを検索するQueryは、別々で走る。
(CakePHPのデバッグツールの、SqlLogを見てもらえば、どういうQueryが流れているのか一目瞭然で便利。)
CakePHPが、 hasMany
のデータを別で取ってきてくれて、元データの配列にがっちゃんこして返してくれる。
でもこれだと、 paginate
などを利用して一覧画面を作りたいときに、同Queryじゃないのでソートが効かなくなる。
最新公開予約日時もソートしたいので、同Queryでデータを取ってこられるようにしたい。
hasOne
指定したい
まずは、取得条件だけを指定した hasOne
指定をしてみる。(取得条件は、先に述べた仕様の通り)
#/Model/Table/記事Table.php $this->hasOne('記事公開予約日時', [ 'className' => '記事公開予約日時', 'foreignKey' => '記事_id', 'conditions' => function (QueryExpression $exp, Query $query) { return $exp->add(['記事公開予約日時.公開予約日時 >' => Time::now()]); }, ]);
- ※ 'conditions' のクロージャーを書く場合のメモ
- 引数
QueryExpression $exp, Query $query
は必須。 - use Cake\ORM\Query;
- use Cake\Database\Expression\QueryExpression;
- returnは、QueryExpressionオブジェクト or 空配列
- 引数
- ※
Time::now()
=現在日時- use Cake\I18n\Time;
デバッグ出力された、Queryを確認すると、ちゃんとLEFT JOINされていて、同SQLでデータを取得できている。
SELECT 記事.記事_id, : : 記事公開予約日時.記事_id, 記事公開予約日時.公開予約日時, 記事公開予約日時.更新日時, : : FROM 記事 LEFT JOIN 記事公開予約日時 ON ( 記事公開予約日時.公開予約日時 > '2018-07-09 14:30:32' AND 記事.記事_id, = ( 記事公開予約日時.記事_id, ) ) :
でも、このSQLだと、
同じ記事_idで、かつ、公開予約日時が未来日のものが複数あったら、
データが重複してしまうし、最新の1件ってわけではない。
(一応、仕様ではそういうデータはありえないってことにはなっているけど・・・)
理想は、記事公開予約日時.公開予約日時 を サブクエリで取ってきたい。limit(1)で。
hasOne
設定を弄り倒す
正直、この辺のやり方は、CakePHPの公式では見つけられなかった。
この辺のやり方や、解説が公式にもあると嬉しいんだけど。
ネットを徘徊してて見つけた、Stack Overflowの投稿が参考になった。
いろいろ試行錯誤して、できた hasOne
設定がこれ。
#/Model/Table/記事Table.php $this->hasOne('記事公開予約日時', [ 'className' => '記事公開予約日時', 'foreignKey' => '記事_id', 'conditions' => function (QueryExpression $exp, Query $query) { $subquery = $query ->connection() ->newQuery() ->select(['latest記事公開予約日時.更新日時']) -- ★1 ->from(['latest記事公開予約日時' => '記事公開予約日時']) ->where([ 'latest記事公開予約日時.記事_id = 記事.記事_id', -- ★2 'latest記事公開予約日時.公開予約日時 >' => Time::now(), ]) ->order(['latest記事公開予約日時.公開予約日時' => 'DESC']) ->limit(1); return $exp->add([ '記事公開予約日時.更新日時 = ' => $subquery ]); } ]);
非常にややこしい。
説明すると、
記事_idに紐付いていて、かつ、
公開予定日が未来日の記事公開予約日時テーブルのデータを、
公開予約日時で降順ソートして、1件だけ取得する。
結果(サブクエリ)を、記事公開予約日時テーブルとして記事テーブルにLEFT JOIN。
この、LEFT JOINするときに、ON条件で、何か唯一の条件になるフィールドが必要なのだが、
悲しいことに、記事公開予約日時テーブルは複合主キーである。
ON条件で、複合主キーを指定するやり方を、私は知らない。
なので、苦肉の策で、更新日ならユニークだろうということで、
記事公開予約日時.更新日時でON指定している。(★1)
更に注意するところは、サブクエリなどで、whereに別テーブルのフィールドを参照させたい時は、
配列で指定しないで、まるっとStringで条件を書くこと。(★2)
配列で
->where([ 'latest記事公開予約日時.記事id' => '記事.記事id'])
のように指定してしまうと、SQLのwhere句で
latest記事公開予約日時.記事id = 記事.記事id
こうならずに、
latest記事公開予約日時.記事id = '記事.記事id'
文字列の '記事.記事_id'
と比較してしまうSQLが出来上がる。
この辺は、公式にも説明がある。
・・・と思ったら、載ってなかった。
はて。どこかで見た気がするのだが。Stack Overflowだったか?
ぬーむ。
↓吐き出されたSQLがこちら。一応これで、やりたいことはできた。
SELECT 記事.記事_id, : : 記事公開予約日時.記事_id, 記事公開予約日時.公開予約日時, 記事公開予約日時.更新日時, : : FROM 記事 : LEFT JOIN 記事公開予約日時 ON ( 記事公開予約日時.更新日 = ( SELECT latest記事公開予約日時.更新日時 FROM 記事公開予約日時 latest記事公開予約日時 WHERE ( latest記事公開予約日時.記事_id = 記事.記事_id AND latest記事公開予約日時.更新日時 > '2018/07/09 16:07' ) ORDER BY latest記事公開予約日時.公開予約日時 DESC LIMIT 1 ) AND 記事.記事_id = ( 記事公開予約日時.記事_id ) ) WHERE : :
最後に、 paginate
のソート設定 > sortWhitelist
に追加するのを忘れない
paginate
のソート機能はデフォルトで使う場合、同テーブル内のデータに限定されている。
テーブルをまたいで、ソートさせたい時は、 sortWhitelist
指定をController内に書く。
例)
public $paginate = [ 'limit' => 100, 'sortWhitelist' => [ 記事.記事_id, 記事公開予約日時.記事_id, 記事公開予約日時.公開予約日時, 記事公開予約日時.更新日時, ], 'order' => [ '記事.記事_id' => 'desc', ], ];