CakePHP3でCASE WHEN EXISTSを作る

環境

• PHP 7.0.9
• CakePHP 3.3.4
• MySQL 5.6.30

問題

コードと手間を減らすために、selectのCase文でEXISTSを使うSQLを構築する必要がありました。
公式にはCase文とEXISTSの書き方は記載されてますが、それらを組み合わせた書き方は記載されておらず、四苦八苦したのでメモを残しておきます。

Case文の作り方

公式のCase文にもあるように、例えばSELECT CASE WHEN status is null THEN 'red' ELSE 'green' END AS 'result' FROM cities;というSQLを作りたい場合は、

# SELECT CASE WHEN status is null THEN 'red' ELSE 'green' END AS 'result';
$query = $this->Countries->find()
    ->select(['result' => function($q){
      return $q->newExpr()->addCase(
        [$q->newExpr)->add(['status is'=>null])],
        ['red','green'],
        ['string']
      )
    }]);

のように書けます。

EXISTSの作り方

公式のEXISTSでは、一度サブクエリを作成してからwhere句で構築を行っています。

# SELECT id FROM Cities WHERE Countries.id = Cities.country_id AND Cities.population > 100; 
$subquery = $this->Cities->find()
    ->select(['id'])
    ->where(function($exp,$q){
      return $exp->equalFields('Countries.id','Cities.country_id');
    })
    ->addWhere(['Cities.population >'=> 1000]);

#where句でsubqueryを使う
$query = $this->Countries->find()
  ->where(fucntion($exp, $q) use ($subquery){
    return $exp->exists($subquery);
  });

#WHERE EXISTS(SELECT id FROM Cities WHERE Countries.id = Cities.country_id AND Cities->population > 100)

CASE WHEN EXISTSの作り方

公式の例ではwhere句でexistsを使っていますが、今回はselect句で使いたい、かつCase文も使う必要がります。
ネストが深くなると混乱の元なので、サブクエリは別で定義することにしました。サブクエリは上記と同じものを使用して、

# SELECT id FROM Cities WHERE Countries.id = Cities.country_id AND Cities->population > 100; 
$subquery = $this->Cities->find()
    ->select(['id'])
    ->where(function($exp,$q){
      return $exp->equalFields(''Countries.id',Cities.country_id');
    })
    ->addWhere(['Cities.population >'=> 1000]);

#select句でCASEとEXISTSを使う
$query = $this->Countries->find()
  ->select(function($q) use ($subquery){
    return [
      'id',
      'name',
      'result' => $q->newExpr()->addCase(
          [$q->newExpr()->add([$q->newExpr()->exists($subquery)])],
          ['red', 'blue'],
          ['string']
        )
      ];
  });

このクエリビルダーで作られるSQLは、こんな風になるはずです。

SELECT
 id,
 name,
 CASE WHEN EXISTS(
    SELECT id FROM Cities WHERE Countries.id=Cities.country_id AND Cities.population > 1000
    ) THEN 'red'
  ELSE 'green' END AS result
FROM Countries;

もやもやする点

上記の例ではCase文の値の型をstringに指定していますが、真偽値(true|false)が欲しい場合boolean型を指定すると、なぜか文字列(例えば"1")が返ってきてしまいます。integerでも文字列となってしまうので、うまくCake\Database\Type\BoolTypeとして変換されてなさそうです。

参考文献

• クエリビルダ - CakePHP3公式ドキュメント