訂正
先日SQLアンチパターンを読んでいたら, そのものズバリのSQLを発見してしまいました
SELECT
a.id
FROM
articles a
INNER JOIN article_tag_relations b ON a.id = b.article_id AND b.tag_id = 1
INNER JOIN article_tag_relations c ON a.id = c.article_id AND c.tag_id = 3
;
指定された全てのタグと内部結合してみるという, スマートかつよく考えたら当たり前の方法で問題は解決します.
ですので, ここから先のくどくどした文章は全て読む必要はありません.
まえがき
動作確認は, 以下の環境で行っています
php:5.4
laravel:5.0
mysql(mariadb): 5.5.47
仕様
ブログに投稿された記事（Article)から, タグ(Tag)をキーに検索する.
記事には0個以上のタグを付けることができる.
検索条件として1個以上のタグを指定することができ, ２個以上のタグが指定された場合,
それらすべてのタグがつけられた記事のみが検索される.
やりたいこと
やりたいことは至って単純明快, ブログの記事をタグで検索するだけ.
その割に, イマイチスッキリした書き方が思い浮かばないので, ２通りくらい書いてみてみます.
もっと他に良いやり方を知ってましたら, 教えてください.
使用するデータ
以下の例では次のようなデータを使用しています.
- 記事テーブル
| id | 記事名 |
|---|---|
| 1 | 記事1 |
| 2 | 記事2 |
| 3 | 記事3 |
| 4 | 記事4 |
- タグテーブル
| id | タグ名 |
|---|---|
| 1 | タグ1 |
| 2 | タグ2 |
| 3 | タグ3 |
- 記事-タグ関連テーブル(記事ID, タグIDにはそれぞれインデックスが張られています)
| id | 記事ID | タグID |
|---|---|---|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 3 | 3 |
| 6 | 3 | 1 |
| 7 | 4 | 1 |
| 8 | 4 | 2 |
| 9 | 4 | 3 |
PHP側で処理の大半をを行う場合
コード全文はこんな感じ
<?php
use Illuminate\Support\Facades\DB;
use App\Article;
use App\Tag;
class ArticleTest extends TestCase {
/**
* @setUp
*
*/
public function setUp()
{
parent::setUp();
$this->initialize();
}
/**
* A basic functional test example.
*
* @return void
*/
public function testManyToMany_PHP側に処理を書く()
{
$searchTagIds = ['1', '3'];
$articles = $this->getArticles($searchTagIds[0]);
$found = [];
foreach ($articles as $article) {
if ($this->hasTags($article, $searchTagIds)) {
$found[] = $article->id;
}
}
$expected = ['3', '4'];
$this->assertSame($expected, $found, '見つかった記事のIDリストで比較');
}
/**
* 対象タグを持つ記事を取得する
*
*/
private function getArticles($searchTagId)
{
return Article::with('tags')
->whereHas('tags', function($query) use ($searchTagId) {
$query->where('tags.id', '=', $searchTagId);
})
->get();
}
/**
* 記事が対象のタグを全て持っているかを返す
* このメソッドは本来Articleモデルのメソッドとなるべきものだろう
*
*/
private function hasTags($article, $searchTagIds) {
foreach ($searchTagIds as $searchTagId) {
if (!$article->tags->contains('id', $searchTagId)) {
return false;
}
}
return true;
}
/**
* DB初期化
*/
private function initialize()
{
DB::connection()->table('articles')->truncate();
DB::connection()->table('tags')->truncate();
DB::connection()->table('article_tag_relations')->truncate();
$article1 = new Article();
$article1->name = '記事1';
$article1->save();
$article2 = new Article();
$article2->name = '記事2';
$article2->save();
$article3 = new Article();
$article3->name = '記事3';
$article3->save();
$article4 = new Article();
$article4->name = '記事4';
$article4->save();
$tag1 = new Tag();
$tag1->name = 'タグ1';
$tag1->save();
$tag2 = new Tag();
$tag2->name = 'タグ2';
$tag2->save();
$tag3 = new Tag();
$tag3->name = 'タグ3';
$tag3->save();
$article1->tags()->save($tag1);
$article1->tags()->save($tag2);
$article2->tags()->save($tag2);
$article2->tags()->save($tag3);
$article3->tags()->save($tag3);
$article3->tags()->save($tag1);
$article4->tags()->save($tag1);
$article4->tags()->save($tag2);
$article4->tags()->save($tag3);
}
}
getArticlesメソッドで, ArticleモデルをTagモデル付きでざっくりとデータを取得し
/**
* 対象タグを持つ記事を取得する
*
*/
private function getArticles($searchTagId)
{
return Article::with('tags')
->whereHas('tags', function($query) use ($searchTagId) {
$query->where('tags.id', '=', $searchTagId);
})
->get();
}
その後, Articleそれぞれに対してタグリストを持っているかどうかのチェックを行う
/**
* 記事が対象のタグを全て持っているかを返す
* このメソッドは本来Articleモデルのメソッドとなるべきものだろう
*
*/
private function hasTags($article, $searchTagIds) {
foreach ($searchTagIds as $searchTagId) {
if (!$article->tags->contains('id', $searchTagId)) {
return false;
}
}
return true;
}
この例では, それなりにすっきりとした書き方ができたので, 何をやっているかを理解するのはそれほど面倒ではないと思われる.
しかし, 処理がDB半分PHP半分みたいな形になっているので, 何らかのバグが有った場合にどちら側に
問題があったのかは調べないとわからない.
また, 多少は絞込を行っているとはいえ, 記事数が大きくなった時に時間的/空間的なボトルネックになってしまう可能性もある.
-
メリット
コードは理解しやすい（多分) -
デメリット
不具合発生時に調査が面倒になる（かも知れない）
パフォーマンス上のボトルネックになる可能性がある
DB側で処理の大半を行う場合
DBで処理しようと考えた場合, まず第一に,
複数のタグIDを全て持つ記事IDを検索するSQLはどう書くべきか?
という問題があると思うのですが, 今回は以下の様なSQLで考えてみます.
（ここでは例としてsearchTagIds = [1,3]の場合を考えます)
SELECT
a.article_id
FROM
article_tag_relations a
WHERE
a.tag_id in (1, 3)
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
;
これ自体は, よくある集計用SQLみたいなものなので特に難しいところは無いと思います.
しかし, これをPHP側で書いてみるとどうなるかというと...
public function testManyToMany_DB側に処理を書く()
{
$searchTagIds = ['1', '3'];
$query = DB::table('article_tag_relations')
->whereIn('tag_id', $searchTagIds)
->groupBy('article_id');
foreach ($searchTagIds as $searchTagId) {
$query = $query->havingRaw('MAX(CASE tag_id WHEN ? THEN 1 ELSE 0 END) = 1')->addBinding($searchTagId, 'having');
}
$found = $query->lists('article_id');
$expected = ['3', '4'];
$this->assertSame($expected, $found, '見つかった記事のIDリストで比較');
}
こんな感じになると思う.
この方法の良い点は, 必要な処理はSQL上で行っているので, PHP側に全部データをロードしていたやり方に比べて
必要メモリの面では間違いなく優位でしょうし, 速度的にも上でしょう(こっちは計測しないとはっきりしませんが)
悪い点は, 見ての通り何をやっているのは即座には判断しかねるであろう点でしょう.
上の記述では, SQLに不慣れな人間だけでなく, 慣れている人間も一瞬何だこりゃと思ってしまうのではないでしょうか?
こう書くくらいならもう
public function testManyToMany_DB側に処理を書く()
{
$searchTagIds = ['1', '3'];
$query = 'select ';
$query .= ' article_id ';
$query .= 'from ';
$query .= ' article_tag_relations ';
$query .= 'where ';
foreach ($searchTagIds as $i => $searchTagId) {
if ($i > 0) {
$query .= ' OR ';
}
$query .= ' tag_id = ? ';
}
$query .= 'group by ';
$query .= ' article_id ';
$query .= 'having ';
foreach ($searchTagIds as $i => $searchTagId) {
if ($i > 0) {
$query .= ' AND ';
}
$query .= 'MAX(CASE tag_id WHEN ? THEN 1 ELSE 0 END) = 1';
}
$binds = array_flatten([$searchTagIds, $searchTagIds]);
$found = [];
foreach(DB::select($query, $binds) as $row) {
$found[] = $row->article_id;
}
$expected = ['3', '4'];
$this->assertSame($expected, $found, '見つかった記事のIDリストで比較');
}
と発行したいSQLを直接書いたほうが意図が通じやすい可能性があるような気もします.
（まあ上の例でもSQLが断片化されすぎてわかりにくいには変わりませんが）
ということで, まとめると
-
メリット
パフォーマンス的にはPHPで処理するより良いはず
不具合時の原因究明が多少は楽 -
デメリット
何をやりたいのかの意図を, コードそのもので伝えにくい
どちらが良いのか
読みやすさ重視こそモダンなプログラミングだと思うので, PHP側で処理したほうがいいのかな.
もともとこの記事は, 仕事場で見かけた似たような処理が, 一旦記事を全件取得した後で
ごちゃごちゃとした処理が書かれたダサい書き方だったので, こういう時はSQLで書いちゃったほうがすっきりするよ,
と説得するために書きだしたのですが書き終わってみると,
PHP側で書いたほうがすっきりとした記述になるという結論になってしまいました.
まあ、パフォーマンス的にはSQLで書いたほうが早いはず（要計測）なので,
どうも速度的メモリ的に問題があるって場合は, こういうやり方もあるってことを思いだして下さると幸いです.