webプログラムを組む際にはデータベースとの連結は必須です。
かのワードプレスやEC-CUBEといったメジャーなプログラムはすべてデータベースと連結して豊富な機能を実現しています。
私も請謁ながらwebプログラム開発をさせていただくことがしばしばございます。その際にMYSQLと連結させて開発しています。
今回はちょっとしたことですが、役立ったテクニックがありましたので、忘備録として記録いたします。
今回やりたかったこと
データベースには異なったテーブル同士を「結合」して参照する方法があります。マイクロソフトアクセスでいうところの「クエリ」です。
結合方法にはいろいろあるのですが、今回は「LEFT JOIN」で1対複数のデータを結合します。
例えば1対10で結合したとすると、SELECTで最低10行のデータが読み込みされます。
今回これを1行で読み込みしたいと思いました。
早速ググって調べました。
たいていのことはググって調べると出てきます。
今回もちゃんと出てきました。
それによると「 GROUP_CONCAT」関数を使用するとできるとありました。
CONCATってエクセルにもある関数なのでイメージが付きます。
文字結合です。
ということで、MYSQLで実験してみました。
テストテーブルとデータは以下の通りです。
上位のAテーブルと、下位のBテーブルを準備ました。
それぞれ中身が入っています。
Bテーブルの中身はAテーブルのbase01と結合する形です。
通常のLEFT JOINで読み込みするとこうなります。
LEFT JOINでセレクトすると、以下のように複数行にわたって読み込みされます。
■SQL文
SELECT a.*, b.comment FROM a LEFT JOIN b ON a.no = b.link_no
Bテーブルが3件あるので、結合して読み込むと全部で4行になります。
これはこれでその通りです。
今回base01 に結合しているsub01～sub03の3件を1件にまとめます。
GROUP_CONCAT を使って、テーブルBを一つにまとめます。
ではSQL文に GROUP_CONCAT 関数を追加してまとめて読み込みを実行してみます。
■SQL文
SELECT a.*, GROUP_CONCAT(b.comment) FROM a LEFT JOIN b ON a.no = b.link_noFROMの前にGROUP_CONCATを追加します。
予定通り3行になっていたデータが1行にまとまりました。
ってあれ、base02データが読み込みされていません。
LEFT JOINにしているのは、結合データがなくても全てのデータを読み込みしたいのにこれでは具合が悪いです。
何がいけないのでしょうか?
原因は文法不足
GROUP_CONCATを使う場合は、GROUP BY 文が必要です。
それが抜けていたため、base02が呼び出しされなかったのです。
そこで、GROUP BY文をつけて呼び出しします。
■SQL文
SELECT a.*, GROUP_CONCAT(b.comment) FROM a LEFT JOIN b ON a.no = b.link_no GROUP by a.no
GROUP BY 文を付け足すことでちゃんと読み込みされるようになりました。
ソートして読み込みしたい場合はORDER BY文を付け足します。
実用的なことを言えば、データのソートが必要になります。
そこで、ORDER BY文を付け足します。
■SQL文
SELECT a.*, GROUP_CONCAT(b.comment) FROM a LEFT JOIN b ON a.no = b.link_no ORDER by no GROUP by a.noソートを付け足せばOKです。
あれ、文法エラーになりました。
なぜでしょうか?
原因はORDER BYとGROUP BYの表記順番
文法エラーになるのは、ORDER BY文とGROPU BY文の表記順番が間違っているためです。
この2つの文には意外にも表記順番があります。
それがこちらです。
■SQL文
SELECT a.*, GROUP_CONCAT(b.comment) FROM a LEFT JOIN b ON a.no = b.link_no GROUP by a.no ORDER by noなんだかんだと言いつつ、GROUP BYを先にして、ORDER BYを後にします。
うまく呼び出しができました。
SQL文は組み合わせを考えて効率よく
今回は1対複数結合を1行にまとめるという目的でGROUP_CONCATを使用しました。そもそも結合データをキーワード検索をする際に、テーブルAだけの情報を出力したいと考えたのですが、通常LEFT JOINだと同じデータが複数表示されるため、試行錯誤しました。
あとから、サブクエリで検索したほうが早くないか？と気づきサブクエリに変えました。
ただ、サブクエリはケースによっては重くなることもあるため、必ずしもサブクエリが良いとも言えません。
そういった意味では、GROUP_CONCATは利便性の高い関数であることを体験できました。