環境
- 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
として変換されてなさそうです。