CakePHP と Yii で MySQL のサブクエリを使う

## はじめに
CakePHP 2.x と Yii 2.x で MySQL のサブクエリを使ってみます。サンプルのデータベースとして MySQL のサイトにある [Example Databases](http://dev.mysql.com/doc/index-other.html) の world database (InnoDB version) を使うことにします。

## 環境
* PHP 5.4.38
* MySQL 5.6.23
* Yii 2.0.3
* CakePHP 2.6.2

## サンプルのデータベーステーブルの詳細

```sql
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のサブクエリは遅いのか](http://nippondanji.blogspot.jp/2009/03/mysql_25.html) で書かれている「人口が特定の都市に半分以上が密集しているアジアの国」を条件として、その国名を引っ張ってくることにします。

直接書いた場合は以下のようになります:

```sql
SELECT Name FROM Country WHERE Continent = 'Asia' AND Code IN (SELECT CountryCode FROM City WHERE Population > Country.Population / 2);
```

## CakePHP でのサブクエリ

とりあえず Country と City モデルを作成します:

```php:Country.php
App::uses('AppModel', 'Model');

class Country extends AppModel {
	public $useTable = 'Country';
	public $primaryKey = 'Code';
}
```

```php:City.php
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](http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#sub-queries) に書き方が記載されています:

```php:CountryController.php
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;
	}
}
```

クエリは以下:

```sql
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 モデルを生成します:

```php:Country.php
namespace app\models;

use Yii;

class Country extends \yii\db\ActiveRecord
{
    public static function tableName()
    {
        return 'Country';
    }
    ...
}
```

```php:City.php
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](http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html) 辺りで見つけることができます:

```php:CountryController.php
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のサブクエリは遅いのか](http://nippondanji.blogspot.jp/2009/03/mysql_25.html) で書かれているような EXPLAIN で実行計画を調べた際の select_type も 5.1 と 5.6 では値が異なっていたりもしました。

遅いからインデックス貼ろうとか、サブクエリじゃなくて JOIN でやろうとかを考える場合、MySQL のバージョンやクエリのパフォーマンスをちゃんと調べておかないと、無駄なインデックスなどが貼られっぱなしだったりする恐れもあるので、いろいろ注意が必要かなと思います。

## 参考リンク

* [CakePHP Cookbook 2.x Model > Retrieving Your Data > Sub-queries](http://book.cakephp.org/2.0/en/models/retrieving-your-data.html#sub-queries)
* [Yii 2 Guide > Query Builder](http://www.yiiframework.com/doc-2.0/guide-db-query-builder.html)
* [漢(オトコ)のコンピュータ道: なぜMySQLのサブクエリは遅いのか](http://nippondanji.blogspot.jp/2009/03/mysql_25.html)
* [漢(オトコ)のコンピュータ道: 開発スピードアクセル全開ぶっちぎり！日本よ、これがMySQL 5.6だッ！！](http://nippondanji.blogspot.jp/2012/10/mysql-56.html)