おかか梅干し

白米が好きすぎる

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の投稿が参考になった。

stackoverflow.com


いろいろ試行錯誤して、できた 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',
   ],
];