今、VちゃんではGoでコードを書いており、その中でDBにアクセスするコードではクエリビルダーは使わずSQLを直書きしている
書き込み系のSQLは基本的にシンプルになるので特に問題ない
しかし、読み取り系の特定条件での検索をするようなSQLを書くときにはSQLが複雑になり、そのままSQLを文字列として扱うだけでなく、特定のJOINを追加したりWHEREを追加したり、ということが起こる
こういった検索クエリは検索エンジンに投げることが一般的に多いと思うが、今の自分のアプリケーションは検索エンジンを入れているわけではないのでここをSQLで頑張る必要がある
まずはtext/templateの使い方
import (
"bytes"
"fmt"
"testing"
"text/template"
)
func TestExample(t *testing.T) {
var buf bytes.Buffer
tmpl := `
{{ if .flag }}
true
{{ else }}
false
{{ end }}
`
data := map[string]interface{}{
"flag": true,
}
tm := template.Must(template.New("tmpl").Parse(tmpl))
if err := tm.Execute(&buf, data); err != nil {
t.Fatal(err)
}
fmt.Println(buf.String())
}
出力例
true
{{ if .flag }} などを書いているところの改行はそのまま出力されるので注意
html/template というのもあるのでたまにimport間違えたりするので注意
複数条件は以下のように書く
func TestExample(t *testing.T) {
var buf bytes.Buffer
tmpl := `
{{ if and .flag1 .flag2 }}
true
{{ end }}
`
data := map[string]interface{}{
"flag1": true,
"flag2": true,
}
tm := template.Must(template.New("tmpl").Parse(tmpl))
if err := tm.Execute(&buf, data); err != nil {
t.Fatal(err)
}
fmt.Println(buf.String())
}
and の後に条件を並べる
and に否定条件などを入れる場合は and (not .flag1) (not flag2) みたいにかっこで囲っておく
ドキュメントを見ると比較演算子を使えたりいくつかの演算子がtemplate内での独自構文で提供されている
golang.org
実際にSQLを書いていく
SELECT
vtuber.id
FROM
vtuber
WHERE
vtuber.deleted_at IS NULL
これは実際のGoのアプリケーションでsqlxで実行されるSQLをシンプルにしたもの
VTuberの一覧を取得しているSQLである
一覧を取得するときにはここに検索条件が追加される
例えばある個性が設定されているVTuberのみ取得するSQLにしてみよう
SELECT
vtuber.id
FROM
vtuber
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
WHERE
vtuber.deleted_at IS NULL
AND vtuber_personality.personality_id IN (:personalities)
個性の検索をつけることで関連テーブルへのJOIN、WHEREでの絞り込みが追加された
更に配信スタイルでの絞り込みも行ってみよう
SELECT
vtuber.id
FROM
vtuber
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
WHERE
vtuber.deleted_at IS NULL
AND vtuber_personality.personality_id IN (:personalities)
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
条件に全て合致していることを確認するためにGROUP BYが追加された
こんな感じで検索のクエリは条件が追加されるたびに少しづつ複雑さを増していく
じゃあこのSQLをアプリケーション内で実行しようと思ったときにいくつかの選択肢が浮かぶ
- SQLを文字列連結で動的に変える
- クエリビルダーを使う
- SQL自体は変わらないようにSQL内で条件分岐を活用する（WHEREの条件などで頑張る）
それぞれの方法は良し悪しあるかと思うが今回はこのSQLをtext/templateパッケージを使用して組み立ててみようと思う
text/templateでSQLを組み立てる
上記の条件を元にSQLが組み立てられるようにしたtemplateはこのようになる
SELECT
vtuber.id
FROM
vtuber
{{ if .personalities }}
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
{{ end }}
{{ if .vtuberGroups }}
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
{{ end }}
WHERE
vtuber.deleted_at IS NULL
{{ if .personalities }}
AND vtuber_personality.personality_id IN (:personalities)
{{ end }}
{{ if .vtuberGroups }}
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{ end }}
{{ if or .personalities .vtuberGroups }}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{ end }}
Goで実行するとするとこんな感じ
func TestExample(t *testing.T) {
tmpl := `
SELECT
vtuber.id
FROM
vtuber
{{ if .personalities }}
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
{{ end }}
{{ if .vtuberGroups }}
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
{{ end }}
WHERE
vtuber.deleted_at IS NULL
{{ if .personalities }}
AND vtuber_personality.personality_id IN (:personalities)
{{ end }}
{{ if .vtuberGroups }}
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{ end }}
{{ if or .personalities .vtuberGroups }}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{ end }}
`
var buf bytes.Buffer
data := map[string]interface{}{
"personalities": []string{"p1"},
"vtuberGroups": []string{"g1"},
}
tm := template.Must(template.New("tmpl").Parse(tmpl))
if err := tm.Execute(&buf, data); err != nil {
t.Fatal(err)
}
fmt.Println(buf.String())
}
出力されるSQLはこちら
実際にはこの buf.String() をsqlxなどの実行クエリとして流し込むことになる
SELECT
vtuber.id
FROM
vtuber
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
WHERE
vtuber.deleted_at IS NULL
AND vtuber_personality.personality_id IN (:personalities)
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
改行が変に入るのが気になるところ
何も指定しなかった場合、さらにこんな感じになる
SELECT
vtuber.id
FROM
vtuber
WHERE
vtuber.deleted_at IS NULL
そんなときは {{ if .personalities -}}～～～{{- end }} のように波かっこに改行やスペースを削除したいところにハイフンを記述してあげればマシになる
golang.org
SELECT
vtuber.id
FROM
vtuber
{{ if .personalities -}}
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
{{- end }}
{{ if .vtuberGroups -}}
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
{{- end }}
WHERE
vtuber.deleted_at IS NULL
{{ if .personalities -}}
AND vtuber_personality.personality_id IN (:personalities)
{{- end }}
{{ if .vtuberGroups -}}
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
{{- end }}
{{ if or .personalities .vtuberGroups -}}
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
{{- end }}
これを実行すると
SELECT
vtuber.id
FROM
vtuber
INNER JOIN vtuber_personality
ON vtuber_personality.vtuber_id = vtuber.id
INNER JOIN vtuber_video_style
ON vtuber_video_style.vtuber_id = vtuber.id
WHERE
vtuber.deleted_at IS NULL
AND vtuber_personality.personality_id IN (:personalities)
AND vtuber_video_style.vtuber_style_id IN (:video_styles)
GROUP BY vtuber.id
HAVING COUNT(*) = :condition_count
うんうん、条件指定した場合は綺麗になった
条件未指定の場合は？
SELECT
vtuber.id
FROM
vtuber
WHERE
vtuber.deleted_at IS NULL
まあまあ改行入ってしまう
ここは微妙なところ
所感
- template内に全ての条件が入っているので条件の全容を把握しやすい
- ただしちょっと見にくい
- パッとコピーしてSQL流して試すとかはやりにくい
- シンタックスハイライトが効かないかもね、SQLかつtext/templateの構文が混ざるので
