LoginSignup
14
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 5 years have passed since last update.

CakePHPAdvent Calendar 2018Day 8
@o0h(Hideki Kinjyo)

CakePHP3の「Query」と仲良くなる記事 / あるいは変なクエリの書き方

Last updated at Posted at 2018-12-08

この記事は CakePHP Advent Calendar 2018 - Qiita の8本目です。
今の所Cake2系の話が多そうですね！

イントロ

CakePHP3が出てから4年弱が経ちましたが、やはり「2と3の違い」の1つとして(言われる|感じる)ことの一つはモデル周りの大改革です。
ざっくりとした説明として使われる表現でよくあるのは「Modelが2つに分かれて、TableとEntityになった」というものではないでしょうか1。これは概ね間違っていないし、自分が初めてCake3を触ったときの印象もそれであり、ただ同時に「正しくないな」とも感じます。

少なく見積もっても「ORM」の話をするなら、もう1つの役者を忘れてはならないぜ！という気持ちがあります。それが Queryです。
Queryを制するものがFat Controllerとの戦いに勝つかもしれません。

今回は、そんなQueryに1人でも多く振り向いてもらいたいな！という気持ちで、紹介文を書いていきたいと思います。
なお、SELECTの話しかしません。

Queryってなーに; 入門編

Queryは「クエリ」をCakePHPの世界に存在させるための手段です。
通常、Tableオブジェクを介して呼び出します。

$UsersTable->find();

これは、データベースに渡す際に

SELECT * FROM `users`;

に翻訳されます。2

ということで、 Queryとは、1つのSQL文をPHPオブジェクトに翻訳したもの という風に捉えてしまうのが良さそうです。
そして、「最終的に発行されるSQL文をごにょごにょするための色々な機能を搭載している」とも言えます。

基本的な使い方〜句を増やす〜

SQLということで、句を増やします。
これは内部的にはインスタンスメンバ($query->_parts)として保持されますが、操作したい句に対応する名前のミューテータが、そのままメソッドとして備えられています

$query->select(['email']);
// SELECT `email` from `users`;

$query->where(['age' => 12]);
// SELECT * from `users` WHERE name = 12;

$query->where(['created >=' => '2018-01-01']);
// SELECT * from `users` WHERE created >= '2018-01-01';

$query->order(['name']);
// SELECT * FROM `users` ORDRE BY `name`;

ちょっと踏み込んだ使い方〜副問合せ〜

先程、「Queryは1つのSQL文」に対応すると説明しました。
これはデータベースへの問い合わせ実行(の直前)に解釈・展開されるという事ですが、それまでの間は 自由にQueryオブジェクト同士をネストしていく事が可能 ということになります。
具体的な例をあげると、1つがサブクエリです。

$subQuery = $Users->find()->select('id')->where(['rank' =>  5]);
// SELECT `id` FROM `users` WHERE `rank` = 5;
$query = $Posts->find()->where(['user_id IN' => $subQuery);
// SELECT * FROM `posts` WHERE `user_id` IN (SELECT `id` FROM `users`  WHERE `rank` = 5);

個人的には、この辺りから「オブジェクトとしてのQueryは便利だな〜」という感じがしてきます。CakePHP2のModelではやり辛かったあたり。

ちょっと踏み込んだ使い方〜ユニオン〜

Queryを使うと、UNIONも簡単に使うことができます

$categoryQuery = $Categoris->select(['name']);
$tagQuery = $Tags->select(['name']);
$query = $categoryQuery->union($tagQuery);
// SELECT `name` FROM `categories` UNION SELECT `name` FROM `tags`

応用的なクエリの書き方

さて、この記事はAdvent Calendar上「変なクエリを書く」という枠で作成されています。
image.png

そういった訳で、ここからが本題！
「確かにそういう使い方もできるっぽいけど常習的には使わないかな」くらいの、でも知っていたらいざというときに役に立つかも・・・？という話に触れていきたいと思います。

SQL NO CACHE, SELECT FOR UPDATE

これらはまだcook bookにも載っている内容で、安心安全です。
「SELECTキーワードの直後」「文の最後」に任意の内容を挿入するやり方ですね。

$Users->find()->modifier('SQL_NO_CACHE')->epilog('FOR UPDATE');

これは

SELECT SQL_NO_CACHE * FROM `users` FOR UPDATE

という文を組み立てます。

USE(FORCE) INDEX

闇を広げていきます。

これは、あまり良い方法が見つかってない(結局、生っぽい表現を用いてしまっている・・)のですが。いちおう、次のやり方で実効することは可能です。

$query = $this->Users->find()
    ->from([
        "{$this->Users->getTable()} AS {$this->Users->getAlias()} FORCE INDEX(index_name)"
    ]);

クロス結合

Queryの持つ色々な「句をセットする」メソッドは、引数の最後に $overwrite フラグをオプショナルに持っているものが多くあります。
これは「すでにセットされている clauseを上書きするか」というもので、デフォルトの通りfalseで使うと、既存の値に渡された値をappendしていくことになります。

そして、実は from()も「数を増やせる」ものの1つ。
MySQLの場合はFROM句に複数のテーブルを並べるとクロス結合になります。

$query = $this->Users->find()
    ->select(['post_id' => 'posts.id', 'user_id' => 'users.id'])
    ->from('users')
    ->from('posts') // ->from(['users', 'posts']) でもOK
    ->where('posts.id < 5 AND users.id < 3');

(select() に連想配列を渡すと、 [alias => column] という風に解釈されます)

実際に以下のようなSQLが生成されます

SELECT posts.id AS `post_id`, users.id AS `user_id`
FROM users, postns
WHERE questions.id < 5 AND users.id < 3

得られる結果は以下です

 JSON Data
[  
   {  
      "post_id":"1",
      "user_id":"1"
   },
   {  
      "post_id":"2",
      "user_id":"1"
   },
   {  
      "post_id":"3",
      "user_id":"1"
   },
   {  
      "post_id":"1",
      "user_id":"2"
   },
   {  
      "post_id":"2",
      "user_id":"2"
   },
   {  
      "post_id":"3",
      "user_id":"2"
   }
]

Usersというテーブル(repository)からfind()する！というコンテキストなので、勘違いしやすいのですがfrom()が明示的に呼ばれるまでは、クエリオブジェクトの$_parts['from']はからのままです。

// cake console
>>> $Table->find()->clause('from')
=> []
>>> $Table->find()->from('users')->clause('from')
=> [
     "users",
   ]

そのため、この例の中でも(postsの前に)usersを明示的にセットしています。

FROM句の中にSELECT文を入れる

FROM句の中にSELECT文をネストすることもできます。

$questions = TableRegistry::getTableLocator()->get('Questions')
    ->find()
    ->select(['user_id' => 'user_id', 'content' => 'content'])
    ->where('id < 10');
$answers = TableRegistry::getTableLocator()->get('Questions')
    ->find()
    ->select(['user_id' => 'user_id', 'content' => 'content'])
    ->where('id < 10');
$source = $questions->union($answers);

$query = $this->Users->find()
    ->select(['Posts.user_id', 'Posts.content'])
    ->from(['Posts' => $source]);

これは、次のようなSQLになります

SELECT Posts.user_id AS `Posts__user_id`, Posts.content AS `Posts__content` FROM ((SELECT user_id AS `user_id`, content AS `content` FROM questions Questions WHERE id < 10)
UNION (SELECT user_id AS `user_id`, content AS `content` FROM questions Questions WHERE id < 10)) Posts

いかがでしょう、段々と怪しさが増してきたのではないでしょうか？
ただ、こちらは使い途もあると思っていて、実用例でいうと「UNIONした内容にWHEREをかけたい」という実装の抽象化のために私は利用したことがあります。
テーブルAとスキーマを同じくする複写的なテーブルBがあった際に、「A UNION B」を実行したい場合があります。このとき、AにもBにも同じwhere()をかけてもいいのですが、やや面倒くさいです。そのため、「先にUNIONしてしまって」その集合に外側からconditionを入れてしまうか〜という感じでした。

注意点がいくつかあります。

select()に、 [$field => $field] となるように指定を渡す

デフォルトでは、 sprintf('%s__%s', $alias, $field); となるようにSELECT句が生成されます。(内部実装)
例えば、

/* $Posts->find()->select('id') */
SELECT Posts.id AS `Posts__id`

となります。
そうなると、where()やorder()でフィールドを利用するのにやや不便です。であれば、いっその事、エイリアスも実カラム名と揃えてしまえ！という発想です。

$columns = $table->getScheme()->getColumns();
$query->select(array_combine($columns, $columns));

などとすると、一瞬で全てのカラムを選択したりもできます。

FROMにaliasを当ててあげる

こうすることで FROM (SELECT文) alias として、有効なSQL文を組み立てることができます。

おわり

CakePHP2の「find()したらクエリが実行されて、結果が返ってくる」世界観と比べたら、なんだか複雑になったように感じられるかもしれません。
ですが、格段に便利になっており、「どういうふうに向き合って行けばいいか？」というイメージさえ掴んでしまえば、すぐに仲良くなれると思います！

一方で、「ORMつらい」という声が聞かれるのも確かであり、記述と実処理の見通しの良さ・直感性と抽象性はトレードオフな面があります。この記事の後半で紹介しているよう内容だと、実用に当たってはもはや直にSQL表現しちゃったほうが良くない・・・？という境界線を踏み越えているものも、出てくると思います。
とりわけQuery周りでいうと、以前にあった orWhere() のDeprecated(3.6〜)など、「やっぱり設計を見直そうか」という声が後から出てくるほどにデザインも実装も難しい部分であると感じます。

なのですが！
個人的に業務で利用していて、この「SQLへ翻訳可能なオブジェクトを持ち回せる」という恩恵は強烈に感じていて、内部処理を自分で読むことで「不安」も「安心」「見通せる」という状態へと変えていけるように思います。
そうした中で、あまり豊富にサンプルを並べているわけでもありませんが、「何かここまでできるっぽいぞ」という片鱗を感じてもらえればと、このテーマを選びました。少し面白がって読んでいただけたら幸いです。

それでは、ぜひぜひQueryと一緒にCakePHPで幸せな開発ライフを！🍰

明日は @yucharo さんです！

  1. なお、「TableとEntity」について、CakePHP2学習者向けには「Entityとはなにか」「なぜEntityが必要(役に立つ)か」を抑えるのが肝要かと考えており、そのあたりは以前に書いたブログ記事に自分なりにまとめてあります http://tech.connehito.com/entry/cakephp3-orm-entity ↩

  2. 実施には、「SELECT field1 as Users__field1,field2 as Users__field2 FROM users Users」のようになりますが、簡略化しています。 ↩

14
14
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
14
14

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?