はじめに
CakePHP 2.x と Yii 2.x で MySQL のサブクエリを使ってみます。サンプルのデータベースとして MySQL のサイトにある Example Databases の world database (InnoDB version) を使うことにします。
環境
- PHP 5.4.38
- MySQL 5.6.23
- Yii 2.0.3
- CakePHP 2.6.2
サンプルのデータベーステーブルの詳細
mysql> show tables;
+--------------------+
| Tables_in_subquery |
+--------------------+
| City |
| Country |
| CountryLanguage |
+--------------------+
今回サブクエリの組み立てに使うのは City と Country テーブルのみです:
mysql> SHOW CREATE TABLE City\G
*************************** 1. row ***************************
Table: City
Create Table: CREATE TABLE `City` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`Name` char(35) NOT NULL DEFAULT '',
`CountryCode` char(3) NOT NULL DEFAULT '',
`District` char(20) NOT NULL DEFAULT '',
`Population` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`ID`),
KEY `CountryCode` (`CountryCode`),
CONSTRAINT `city_ibfk_1` FOREIGN KEY (`CountryCode`) REFERENCES `Country` (`Code`)
) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> SHOW CREATE TABLE Country\G
*************************** 1. row ***************************
Table: Country
Create Table: CREATE TABLE `Country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` float(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint(6) DEFAULT NULL,
`Population` int(11) NOT NULL DEFAULT '0',
... 省略 ...
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
どういうサブクエリにするか
どういうサブクエリにするかは なぜMySQLのサブクエリは遅いのか で書かれている「人口が特定の都市に半分以上が密集しているアジアの国」を条件として、その国名を引っ張ってくることにします。
直接書いた場合は以下のようになります:
SELECT Name FROM Country WHERE Continent = 'Asia' AND Code IN (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
CakePHP でのサブクエリ
とりあえず Country と City モデルを作成します:
App::uses('AppModel', 'Model');
class Country extends AppModel {
public $useTable = 'Country';
public $primaryKey = 'Code';
}
App::uses('AppModel', 'Model');
class City extends AppModel {
public $useTable = 'City';
public $primaryKey = 'ID';
}
続いて CountryController.php を作成。簡易ではありますが index アクションを使って、そこでサブクエリの結果を var_dump() してみることにします。CakePHP 2.x でのサブクエリについては Cookbook 2.x の Model > Retrieving Your Data > Sub-queries に書き方が記載されています:
App::uses('AppController', 'Controller');
class CountryController extends AppController {
public function index() {
$db = $this->Country->getDataSource();
$this->loadModel('City');
$subQuery = $db->buildStatement(array(
'fields' => array('City.CountryCode'),
'table' => $db->fullTableName($this->City),
'alias' => 'City',
'conditions' => array('City.Population > Country.Population / 2'),
), $this->Country);
$data = $this->Country->find('all', array(
'fields' => array('Country.name'),
'conditions' => array(
'Country.Continent' => 'Asia',
$db->expression('Country.Code IN (' . $subQuery . ')'),
),
));
var_dump($data);
exit;
}
}
クエリは以下:
SELECT `Country`.`name` FROM `subquery`.`Country` AS `Country` WHERE `Country`.`Continent` = 'Asia' AND Country.Code IN (SELECT City.CountryCode FROM `subquery`.`City` AS `City` WHERE `City`.`Population` > `Country`.`Population` / 2)
正しく書かれていれば以下のような結果になるかと思います:
array (size=3)
0 =>
array (size=1)
'Country' =>
array (size=1)
'name' => string 'Macao' (length=5)
1 =>
array (size=1)
'Country' =>
array (size=1)
'name' => string 'Qatar' (length=5)
2 =>
array (size=1)
'Country' =>
array (size=1)
'name' => string 'Singapore' (length=9)
Yii 2 でのサブクエリ
これもとりあえず Gii で Country, City モデルを生成します:
namespace app\models;
use Yii;
class Country extends \yii\db\ActiveRecord
{
public static function tableName()
{
return 'Country';
}
...
}
namespace app\models;
use Yii;
class City extends \yii\db\ActiveRecord
{
public static function tableName()
{
return 'City';
}
...
}
続いて CakePHP の例と同様にコントローラにお邪魔して actionIndex() にて var_dump() してみます。Yii 2.x でのサブクエリ関連の情報は Guide の Query Builder 辺りで見つけることができます:
namespace app\controllers;
use yii\db\Query;
use app\models\Country;
class CountryController extends \yii\web\Controller
{
public function actionIndex()
{
$subQuery = (new Query)
->select(['CountryCode'])
->from('City')
->where('Population > Country.Population / 2');
$query = Country::find()
->select(['Name'])
->where(['Continent' => 'Asia', 'Code' => $subQuery])
->createCommand();
var_dump($query->sql, $query->params);
var_dump($query->queryAll());
exit;
}
}
SQL、パラメータ、クエリ結果などは以下のようになります:
string 'SELECT `Name` FROM `Country` WHERE (`Continent`=:qp0) AND (`Code` IN (SELECT `CountryCode` FROM `City` WHERE Population > Country.Population / 2))' (length=146)
array (size=1)
':qp0' => string 'Asia' (length=4)
array (size=3)
0 =>
array (size=1)
'Name' => string 'Macao' (length=5)
1 =>
array (size=1)
'Name' => string 'Qatar' (length=5)
2 =>
array (size=1)
'Name' => string 'Singapore' (length=9)
CakePHP, Yii ともにカラムのクオートやテーブルのエイリアスが若干荒い書き方になっているので、そこらへんもうすこし調整しないといけないかもしれません。
MySQL でのサブクエリについて
いろいろ調べていると MySQL 5.6 以前まではサブクエリは重いだとか遅いだとか書かれている記事がけっこうあったんですが、5.6 以降だと、体感的には全く遅さを感じませんでした。また なぜMySQLのサブクエリは遅いのか で書かれているような EXPLAIN で実行計画を調べた際の select_type も 5.1 と 5.6 では値が異なっていたりもしました。
遅いからインデックス貼ろうとか、サブクエリじゃなくて JOIN でやろうとかを考える場合、MySQL のバージョンやクエリのパフォーマンスをちゃんと調べておかないと、無駄なインデックスなどが貼られっぱなしだったりする恐れもあるので、いろいろ注意が必要かなと思います。