あかつきのお宿

ちょっと……というかだいぶ古い情報になってしまうんですが、CakePHP2系のfindでサブクエリを使いたい場合の書き方です。

例えばこんな感じにMySQLのSELECT文を発行したいとしましょう。

SELECT * FROM blogs WHERE user_id IN(SELECT id FROM users WHERE status = 1)

すごい適当なんですがユーザー情報の入ったusersテーブルとブログ記事の情報が入ったblogsという二つのテーブルがあり、blogsにはuser_idというカラムが存在します。そんでstatusが1のユーザーに紐づくブログ記事のデータを取ってきたい、みたいなことですね。

ビヘイビアのContainableとアソシエーションの設定があればこんな風に（↓）簡単にJOINしてデータを持ってくることができますよね。

$params = array(
  'conditions' => array('User.status => 1'),
  'contain' => array('User'),
);
$data = $this->Blog->find('all', $params);

でも場合によってはサブクエリを使いたいことがあるかもしれないので、そういうときはどうするか。

先にコードだけ書いちゃいますが、こんな感じでサブクエリの生成とそれを使ったSELECT文が作成できます。

//サブクエリの生成
$db = $this->Blog->getDataSource();
$subquery = $db->buildStatement(array(
  'conditions' => array('User.status' => 1),
  'fields' => array('User.id'),
  'table' => 'users',
  'alias' => 'User',
));

//SELECT文の発行
$params = array(
  'conditions' => array("Blog.user_id IN({$subquery})"),
);
$data = $this->Blog->find('all', $params);

まずgetDataSourceでデータベースの接続情報やそれに関連するオブジェクトを取ってきます。

そのオブジェクトの中にbuildStatementというメソッドがあるので、これを使ってサブクエリを作成します。設定項目はいろいろあるんですが、最低限上に書いてあるやつだけ設定すればサブクエリは生成できます。

conditionsは検索条件ですね。通常のfindと同じ要領でサブクエリ側のWHERE句に必要な条件を書きます。

fieldsはサブクエリのSELECTで取得したいカラムです。上記の場合はユーザーのIDを取ってくる書き方をしています。

tableとaliasはテーブル名やそのエイリアスです。スペルミスとかを避けたいのであれば$this->{Model}->tableとか$this->{Model}->aliasとかで設定しても良いと思います。

//サブクエリの生成
$db = $this->Blog->getDataSource();
$subquery = $db->buildStatement(array(
  'conditions' => array('User.status' => 1),
  'fields' => array('User.id'),
  'table' => $this->User->table,
  'alias' => $this->User->alias,
));

//SELECT文の発行
$params = array(
  'conditions' => array("Blog.user_id IN({$subquery})"),
);
$data = $this->Blog->find('all', $params);

これで$subqueryに生成されたサブクエリが入るので、それをIN句に突っ込んでfindすればOKです。

わざわざこんなことせずに直接SQLを書いた方が楽な気がしないでもないんですが、サブクエリのWHEREがすごい複雑な場合とかはこっちの書き方の方がすっきりして見やすいかもしれません。あと毎回これを書くのはめんどいのでAppModelにでも適当なメソッドを作って、conditionsとfieldsの中身だけ引数で渡せばサブクエリを作れるようにしておけば結構楽になるかなと思います。



正直サブクエリはめちゃ遅いっていう固定観念があったもんですから、かれこれ10年くらいサブクエリは基本的に使わないようにしてたんですけど、サブクエリがめちゃ遅いってのは今は昔の話みたいで、最近のMySQLはMATELIALIZEDがどうとか実体化がどうとかいう理由でそれなりにスピードの方が改善されているみたいです。実際にIN句を使うよりもサブクエリの方が早い場合もある。

僕自身、かなり膨大なデータを扱う機会があって、初めはIN句で処理していたんだけどそしたらIN句の中に10万件くらいのIDを突っ込むことになってしまって、処理が遅いどころかメモリオーバーで処理が落ちてしまったんですよ。最初はこれどうしようもないなーと思ってたんですけど、物は試しにと思って10年以上見向きもしなかったサブクエリに手を出してみまして……そしたらメモリオーバーもせず、そこまで遅くもなく処理ができるじゃないですかっ。

それが分かってからはIN句の中身が大変なことになってしまう場合に迷わずサブクエリを使う選択をしているんですが、最初はサブクエリをそのまま平文で書いてたんですね。でも「そういえばCakePHPの機能でサブクエリの生成とかできないのかなあ？」と思ってしらべたら上記のやり方が見つかったので、今はこっちの書き方を採用しています。

まあしかし……CakePHP2系ってもうサポートが終了しているから、本当はこんなことをしていないで3系とか4系とかに移行しなきゃいけない気もするんですが……でも2系からの移行ってめっさ大変よね。システムの規模がでかいとたぶん年単位の工数がかかってしまうから「来月くらいまでにちゃっちゃとやっちゃってー」ってわけにもいかないんですよね。

一年かけてサイトリニューアルとかぶっちゃけやりたくねえ。どうしたものかな……。