サブクエリが入っている、複雑なSQLを修正するときの考え方

こんにちわ、PHPエンジニアのエンジニア婦人（@naho_osada）です。
PHPエンジニアとして9年～の経験があります。

今回は「既にある複雑なSQLを修正するときの考え方」を書いていきます。複雑なSQLはサブクエリが入っていてどの条件がどこにかかっている？と悩みがち。
特にフレームワーク等のクエリビルダを使っていると、一見すると何が何やら、どこを触ればいいのかがわかり辛いこともあります。

ここは「分解して考える」のが一番です。結合しているものをひとつずつ分解していきます。いきなり修正するとろくなことになりません。

具体的に、どうするのか、書いていきます。

※サブクエリを使った、複雑なSQLの作り方はこちら→JOINの中で複数JOINする、サブクエリを使ったSQLの作り方

クエリビルダの場合

これから触らなければならないSQLがクエリビルダで書かれているとします。

昨今はフレームワークを使用することが多く、SQLは使用するフレームワークに準拠した書き方をしていることが多いと思います。
この場合、クエリビルダをみただけですぐにわかることは少ないので、一つずつ分解して解釈していくことになります。

生のSQLがすでに分かっている場合、「生のSQLを分解する」に進みます。

クエリビルダの確認

これはZendFrameworkでよくある（と思う）書き方です。

$db = Zend_Db::factory(xxxxx);
$select = $db->select();
$select
	->from(['post' => 'post_data'], ['post_id', 'post_title'])
	->join(['user' => 'user_data'], 'user.user_id = post.author_id', ['user_name'])
        ->join(['auth' => $this->authorBuilder], auth.auth_id = post.author_id, ['auth.*'])
・
・
・

このように、クエリビルダを使っている場合は生のSQLではありません。

そのため、クエリビルダで書かれたものが何をしようとしているものなのか、自分でSQLを書いてみます。

面倒ですが、複雑なものであればあるほど、これをやらないとうまくいきません。

複雑であればあるほど面倒です。本当に面倒です。何度画面をひっくり返そうかと思ったか。

副問い合わせになっているところのSQLを確認する

上の例でいうと「’author_data’ => ‘auth’」のjoinの、「$this->authorBuilder」と書いてあるところです。ここが副問い合わせです。

副問い合わせでは要はSQLを書いているので、このSQLは何をしているのかをまず確認します。こんな感じで中身があったとしましょう。

$db = Zend_Db::factory(xxxxx);
$select = $db->select();
$select
	->from(['auth' => 'author_data'], ['auth.*'])
	->join(['user' => 'user_data'], 'user.user_id = auth.author_id')

・
・
・
return $select;

これをひとつのSQLとして戻すと、こんな感じでしょう。

SELECT * FROM author_data as auth
INNER JOIN user_data AS user ON user.user_id = auth.author_id
・
・
・

このSQLを実行してみて（phpmyadminでもターミナル上でも何でもいいです）、正常に返答が来るか、返答が来たなら何を返してきているのかみます。

副問い合わせの分だけこれを繰り返します。

副問い合わせ以外の本体SQLを確認する

副問い合わせを含まない部分のSQLを書き出します。

ここでは最後の副問い合わせを除いた部分ですね。

SELECT post.post_id, post.post_title, user.user_name FROM post_data as post
INNER JOIN user_data AS user ON user.user_id = post.author_id;

副問い合わせがなければJOINしているだけのSQLですね。

本体SQLと副問い合わせSQLを合体させる

ここまで分解した後、合体させます。

SELECT post.post_id, post.post_title, user.user_name FROM post_data as post
INNER JOIN user_data AS user ON user.user_id = post.author_id;
INNER JOIN
(
	SELECT * FROM author_data as auth
	INNER JOIN user_data AS user ON user.user_id = auth.author_id
        ・
        ・
        ・
) as auth ON auth.auth_id = post.author_id
・
・
・

実行してエラーがでなければ成功です。

ここまでできれば、このSQLは何をしているのかがわかるでしょう。

取得するカラムが少なくてよくわからない場合は、いったん全部取得の「＊」にしてやってみるとよりわかる場合があります。

生SQLの場合

クエリビルダから解釈する必要がなく、生のSQLがわかっているときは少し楽になります。

これは先の記事「JOINの中でJOINする、複雑なSQLの考え方」にあるSQLです。

一度そのまま実行して何をとってきているか確認したら、さらに理解を深めるために副問い合わせ部分を分解していきます。

SELECT 
	doc.document_id, doc.document_name, 
	stf1.staff_name as name, stf1.department_name as department, stf1.section_name as section, 
	stf2.staff_name as name, stf2.department_name as department, stf2.section_name as section, 
	stf3.staff_name as name, stf3.department_name as department, stf3.section_name as section, 
	stf4.staff_name as name, stf4.department_name as department, stf4.section_name as section, 
	stf5.staff_name as name, stf5.department_name as department, stf5.section_name as section
FROM document as doc 
INNER JOIN doc_target as trg ON doc.document_id = trg.document_id
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf2 ON stf2.staff_id = trg.author_id_2
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf3 ON stf3.staff_id = trg.author_id_3
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf4 ON stf4.staff_id = trg.author_id_4
LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf5 ON stf5.staff_id = trg.author_id_5;

JOINの中を取り出す

JOINの中に書いてある副問い合わせのSQLを分解して取り出します。

ここでは2番目以降のLEFT JOINが該当します。

SELECT * FROM staff as stf
INNER JOIN department as dep ON stf.department = dep.department_id
INNER JOIN section as sec ON stf.section = sec.section_id

ここだけを取り出して眺めてみると何をしたいかがわかるのではないでしょうか。

全体で見ただけではわからなくても、こうして一部部分を見てみるとここでは何をしているのかわかるようになります。

分解したSQLを実行して何が起こるか見てみる

中身をとってきただけなので、まずこの段階ではエラーは発生しないはず（発生したら何が悪いのか見てくださいね）。

何をとってきているのか確認します。

元のJOINは何をとってきたかったのか確認する

分解した副問い合わせの外側をみます。

LEFT JOIN (
	SELECT * FROM staff as stf
	INNER JOIN department as dep ON stf.department = dep.department_id
	INNER JOIN section as sec ON stf.section = sec.section_id
)
as stf1 ON stf1.staff_id = trg.author_id_1

副問い合わせ部分をstf1として、そこで取得したstaff_idとtrgとしたところのauthor_id_1を等号で接続していますね。

まとめ

クエリビルダの場合は生のSQLを作りましょう。
作成したSQLはそれぞれで何をしているのか分解して実行して動作を確認しましょう。
目的に必要な修正はそれから考えましょう。

急がば回れ。

いやもう、複雑なSQLは可読性悪くなるんでやめてくださいよ、何かあったときに色々するの大変でしょう。
だからJOINを深くふかくフカクしないと難しいその検索機能は外しましょう。ね？

…でも改修だからそうも言ってられない。機能を外すわけにはいかない。寧ろ条件を追加するらしい。

こんな複雑なSQL、大事故になりそうだから触りたくない。でも触らないと仕事にならない。そんな人に向けて。