今更の話になりますが, PHPやあるいは他の言語の中でSQLを組み立てようとすると, ひどく面倒な上に
最終的な完成図の見通しもわるくなるものです.
それが単純な文字列操作ではくて, eloquentのクエリビルダを使ったところである程度複雑になってるくると
同じことでしょう.
そこで, かつて同じように文字列操作で生成されていたHTMLが同じ理由でViewに分離されたことにならって
SQLをView機能を使って記述する方法を考えてみたいと思います.
サンプルコードは先日かいたコードを
流用します.
また, 今回書いたコードはマイグレーションも含めてGistにあげてましたので, 試したい場合は適当にコピペして
実行してみてください.
簡単なクエリを実行する場合
次のようなsqlを実行したい場合を考えます
SELECT
a.*
FROM
articles a
INNER JOIN article_tag_relations b on a.id = b.article_id
WHERE
b.tag_id = :tag_id
;
この場合は, 単純にresources/views/queries/Artical/findByTagId.blade.phpという名前で
テンプレートファイルを作り, 上のSQLをコピペし, モデルの方に次のようなメソッドを追加します.
public function findByTagId($tagId)
{
$query = view('queries.Article.findByTagId')->render();
$binding = [':tag_id' => $tagId];
return DB::select($query, $binding);
}
viewヘルパメソッドを使い, テンプレートからViewオブジェクトを生成し,
renderメソッドで文字列化し
任意のselect文を実行するDB#selectメソッドを使って実行し, 結果を取得しているだけのコードです.
ポイントはといえば, DB#selectの第二引数にリストではなく連想配列を渡すことで, 名前付きプレースホルダを
使えるようにしていることくらいですか.
(この挙動はlaravelのマニュアルにははっきり書かれていないのですが, おそらくEloquentの
更にローレベルのライブラリであるPDOの仕様なのかもしれませんが)
これを実際に使ってみると, どんな結果が得られるかというと...
public function testFindByTagId()
{
$tag_id = 1;
$articles = (new Article())->findByTagId($tag_id);
var_dump($articles);
# array(3) {
# [0]=>
# object(stdClass)#317 (4) {
# ["id"]=>string(1) "1"
# ["name"]=>string(7) "記事1"
# ["created_at"]=>string(19) "2016-08-19 16:52:39"
# ["updated_at"]=>string(19) "2016-08-19 16:52:39"
# }
#
# ...
#
#}
}
このようの, SQLの結果はselect句で指定したカラムリストをインスタンス変数として持つオブジェクトのリストとして得られます.
このままでは少し使いにくいので, 結果をArticleモデルのCollectionとして返すようにしておきましょう.
public function findByTagId($tagId)
{
$query = view('queries.Article.findByTagIds')->render();
$binding = [':tag_id' => $tagId];
$articles = [];
foreach (DB::select($query, $binding) as $object) {
$articles[] = Article::newFromBuilder($object, 'mysql');
}
return new Collection($articles);
}
/**
* A basic functional test example.
*
* @return void
*/
public function testFindByTagId()
{
$tagId =1;
$articles = (new Article())->findByTagId($tagId);
// 取得できるはずのモデルを別途取得
$expected = Article::whereIn('id', [1, 3, 4])->get();
$this->assertEquals($expected, $articles, '取得された記事モデルの比較');
# => success
}
これで, 他のメソッドと出力の形式を合わせることができたと思います.
ちなみに, ここでは, DBから取得されたオブジェクトをArticleモデルに変換するために
Model#newFromBuilderメソッドを使っていますがこのやり方が正しいかどうかは不明です.
DBから取得したデータを元にモデルを作る, 推奨されるやり方をご存知の方がおりましたら教えていただけると助かります.
複雑なクエリを実行する場合
先日の書いた, 次のsqlを実行したいと思います.
SELECT
a.article_id
FROM
article_tag_relations a
GROUP BY
a.article_id
HAVING
MAX(CASE a.tag_id WHEN 1 THEN 1 ELSE 0 END) = 1
AND MAX(CASE a.tag_id WHEN 3 THEN 1 ELSE 0 END) = 1
;
まず, 呼び出し側のコードから考えてみると...
$articleIds = (new Article())->associatedWithAllTags($tag_ids);
みたいな感じに呼び出したいところです.
すると, テンプレートは
SELECT
a.article_id
FROM
article_tag_relations a
GROUP BY
a.article_id
HAVING
1 = 1
@foreach ($tag_ids as $tag_id)
AND MAX(CASE a.tag_id WHEN :tag_id_{{ $tag_id }} THEN 1 ELSE 0 END) = 1
@endforeach
;
こうなり, このテンプレートを使用するロジックは
public function associatedWithAllTags($tagIds)
{
$params = [];
$params['tag_ids'] = $tagIds;
$query = view('queries.Article.associatedWithAllTags', $params)->render();
$binding = [];
foreach ($tagIds as $tagId) {
$binding[':tag_id_'.$tagId] = $tagId;
}
$ids = [];
foreach (DB::select($query, $binding) as $object) {
$ids[] = intval($object->article_id);
}
return new Collection($ids);
}
こんな感じになるんじゃないかと思います.
実際に実行してみると
public function testAssociatedWithAllTags()
{
$tagIds =[1, 3];
$articles = (new Article())->associatedWithAllTags($tagIds);
# var_dump($articles);
$expected = new Collection([3, 4]);
$this->assertEquals($expected, $articleIds, '取得された記事IDの比較');
}
結果は上々.
テンプレートも元のSQLの形をだいぶ残した形にかけているので,
実行したいSQLの意図は伝わりやすくなっているんじゃないかと思います.
一般化する
似たような処理を何度も書くのも馬鹿らしいので, 処理を一般化してみます.
SQL実行にはテンプレートを完成させるための情報と, プレースホルダに代入する値の２つが必要になるので,
それらを受け取って結果を返すヘルパメソッドを作成します.
/**
* テンプレートファイルからSelect文を読み込み実行する
*
* @param string $templateName テンプレート名
* @param array $templateParams テンプレートファイル用パラメータ
* @param array $value 最終的なSelect文にバインドされる値
* @return array Select文の実行結果
*/
private function selectByTemplate($templateName, $templateParams, $values)
{
$query = view('queries.'.last(explode('\\', get_class($this))).'.'.$templateName, $templateParams)->render();
return DB::select($query, $values);
}
そして, もともとメソッドをこのヘルパメソッドを呼び出す形に修正
public function findByTagId($tagId)
{
$params = [];
$binding = [':tag_id' => $tagId];
$articles = [];
foreach ($this->selectByTemplate(__FUNCTION__, $params, $binding) as $object) {
$articles[] = Article::newFromBuilder($object, 'mysql');
}
return new Collection($articles);
}
public function associatedWithAllTags($tagIds)
{
$params = ['tag_ids' => []];
$binding = [];
foreach ($tagIds as $tagId) {
$params['tag_ids'][] = ':tag_id_'.$tagId;
$binding[':tag_id_'.$tagId] = $tagId;
}
$ids = [];
foreach ($this->selectByTemplate(__FUNCTION__, $params, $binding) as $object) {
$ids[] = intval($object->article_id);
}
return new Collection($ids);
}
テンプレートの名前になる呼び出し元のメソッド名は__FUNCTION__マジック変数を使って渡すことにしました.
selectByTemplateメソッドの中で, debug_backtrace関数経由で直前の関数名を取ることもできるようですが, debugという名前が追加関数を本番用コードで書くのも気持ち悪いので, 明示的に渡す形にしてあります.
最後に, 取得された結果を変形する部分を無名関数として渡すようにします.
public function findByTagId($tagId)
{
$params = [];
$binding = [':tag_id' => $tagId];
$convertFunc = function($object) { return Article::newFromBuilder($object, 'mysql'); };
return $this->selectByTemplate(__FUNCTION__, $params, $binding, $convertFunc);
}
public function associatedWithAllTags($tagIds)
{
$params = ['tag_ids' => []];
$binding = [];
foreach ($tagIds as $tagId) {
$params['tag_ids'][] = ':tag_id_'.$tagId;
$binding[':tag_id_'.$tagId] = $tagId;
}
$convertFunc = function($object) { return intval($object->article_id); };
return $this->selectByTemplate(__FUNCTION__, $params, $binding, $convertFunc);
}
/**
* テンプレートファイルからSelect文を読み込み実行する
*
* @param string $templateName テンプレート名
* @param array $templateParams テンプレートファイル用パラメータ
* @param array $value 最終的なSelect文にバインドされる値
* @param function $convertFunc 取得結果の変換用関数
* @return array Select文の実行結果
*/
private function selectByTemplate($templateName, $templateParams, $values, $convertFunc = null)
{
$query = view('queries.'.last(explode('\\', get_class($this))).'.'.$templateName, $templateParams)->render();
$objects = DB::select($query, $values);
if ($convertFunc === null) {
return new Collection($objects);
}
$converted = [];
foreach ($objects as $object) {
$converted[] = $convertFunc($object);
}
return new Collection($converted);
}
あとは, このselectByTemplateメソッドを各モデルクラスの基底クラスに移動するか,
それともtraitとして独立させて使いたいモデルでuseするようにすれば, 他のモデルでも使えるようになります.
終わりに
ある程度複雑なSQLでも, 原型を残したまま記述できるようになっていると思います.
とにかくSQLを書きたい, 集計処理を書くのにクエリビルダなんて使いたくないという方は, こんなやりかたはどうでしょうか？