Goでtext/templateを使ってSQLを組み立てる

今、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を書く

実際に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の構文が混ざるので

Realforceのキーボードを買った

勝ったのはかなーりまえだけど下書きに残りっぱなしだったので放流

以下付属品各種

他にも選択肢としてHHKBとかあると思うだけど、自分はメインがWindowsなのでWindowsボタンがどうしても欲しいのです

テンキーも使ったとしてもゲームくらい？不要なのでテンキーレス

使ってみて

もう数年使っているが前の標準キーボードには戻れない

音も小さいし打鍵の深さとかも調整出来てめっちゃ楽

文字を打つ時は結構浅めが好きなんだけど、浅いとゲームしているときに結構誤爆する。最初は困ったけど、それも慣れて問題なくなった

色は白がおすすめ、黒の同じものを会社で使っているがキーに書いてある文字が見えにくくてちょっと微妙。かっこいいんだけどね

sqlxでIN句を使う方法

最近Goでsqlxを使っているが、毎回忘れるのでメモ

IN句を用いたクエリ

sqlxでIN句を用いたクエリを使うにはそのままクエリを投げることはできない。
一度 sqlx.In を通してあげる必要がある。

type Hoge struct {
    Id string `db:"id"`
}

db, err := sqlx.Connect("mysql", "接続情報")
if err != nil {
    panic(err)
}

query := `
SELECT id FROM hoge
WHERE id IN (?)
`
idList := []string{"aaa", "bbb", "ccc"}

// 一度sqlx.Inを通してあげる必要がある
query, args, err := sqlx.In(query, idLIst)
if err != nil {
    panic(err)
}
// DBのドライバーに合わせたクエリの変換
query = db.Rebind(query)

var result []*Hoge
err = db.Select(&result, query, args...)
if err != nil {
    panic(err)
}

return result

Selectの結果を別に詰め替えるなら

// 上の結果がresult変数に入っているとして
type Fuga struct {
    Id string
}

// 結果の長さ文の容量を確保したスライスを用意
newResult := make([]*Fuga, 0, len(result))

for _, r := range result {
    newResult = append(newResult, &Fuga{Id: r.Id})
}

return newResult

ここらへんのappendがGo特有というかLLやJavaといった言語ではあまり見ないやり方

IN句に指定する値以外にバインドが必要な場合

SQLに :idList のように名前を指定して値をバインドでき、それはもちろんIN句との併用も可能
以下のようにsqlx.Inとsqlx.Namedを組み合わせてクエリを組み立てていく

type Hoge struct {
    Id string `db:"id"`
}

db, err := sqlx.Connect("mysql", "接続情報")
if err != nil {
    panic(err)
}

query := `
SELECT id FROM hoge
WHERE
    id IN (:idList)
    AND data_type = :dataType
`
idList := []string{"aaa", "bbb", "ccc"}
dataType := "xxxType"

input := map[string]interface{}{
    "idList": idList,
    "dataType": dataType,
}

// 最初にsqlx.Named
query, args, err := sqlx.Named(query, input)

// 次にsqlx.In
query, args, err := sqlx.In(query, args...)
if err != nil {
    panic(err)
}
query = db.Rebind(query)

var result []*Hoge
err = db.Select(&result, query, args...)
if err != nil {
    panic(err)
}

return result

決まった手続きなので以下のようなラッパー関数を作ってもいいかもしれない

func NamedInSql(db sqlx.DB, query string, arg interface{}) (string, []interface{}, error) {
    query, args, err := sqlx.Named(query, arg)
    if err != nil {
        return "", nil, err
    }

    query, args, err = sqlx.In(query, args...)
    if err != nil {
        return "", nil, err
    }
    query = db.Rebind(query)
 
    return query, args, err
}

最近ブログ書いてないな

どーも。
お久しぶりです。

何気なくこのブログの最終更新を見たら3月。
ということで9カ月ぶりの更新です。

なんか最近どこかの記事で、ブログとかアウトプットの敷居を下げていこうみたいなのを見て、そうだなと思い筆をとった次第。

もう3月あたりからはずっとコロナでリモートワークで時間の余裕もあるのと（仕事は物凄く忙しいが）、最近はVちゃん https://vtuber-channel.com/ というVTuber情報サイトを開発していたこともあり技術的にちょっとした知見は貯まったので、適度にアウトプットをしていきたい。

今日はその意思表明ってことで。

PS

VTuberに興味がある人Vちゃん使ってくれ

vtuber-channel.com

現在時刻を扱う処理をJavaScriptで書いてPCの時刻を変更して試すとき、ブラウザによって癖がある

現在時刻を扱う処理をJavaScriptで書くことはよくあると思う。ある日時を過ぎていたらリンクを非活性にしたり。
そんなときにブラウザで試そうと思うとPCの時刻を変えるのが手軽なわけだ。
もちろん現在時刻をモックして変えられる仕組みを導入していればいいのだが、WEBフロントエンドではそういう仕組みを導入しているところは少ないんじゃないかと。

じゃあ試してみようと思ってPCの時刻を変えてChromeで試してみると数十秒待たないとその時刻が反映されなかったりする。
Firefoxで試すと即時反映される。ブラウザごとに違うんだなーってことで一旦2ブラウザでどう変わってくるか試してみた。
検証したOSはWindows。Macとかだとまた違うかもしれない。

ブラウザ	現在時刻	タイムゾーン
Chrome	タブを新しく開けば更新される、そのまま待っていても数十秒更新されない、ページを更新しても数十秒更新されない	即時反映
Firefox	特に更新もせず即時反映	タブを引き直しても反映されない、Firefox自体を落とさないといけない

Windowsとかだと「日付と時刻」というところから「日付を手動で設定する」のところから日時を変更することが出来る。
んで、ブラウザの開発者ツールからConsoleを開いて new Date と叩けば現在時刻が出てくるのでそれで確認。

現在時刻に加えてタイムゾーンの変更時の挙動も検証してみたらちょっと仕様が違っていて面倒だなと。
SafariやEdgeは検証していないが実際に試してみる場合はこのことを頭に入れておかないとハマりそう。

2019年振り返り

せっかくだし簡単に振り返っていく
総評としてはあまり変化のない1年だったなという感じ
忙しかったんだけど何か新しいこととかはあまりやっていないような

仕事

• 延々とその会社特有のものを勉強する1年だった
• 例えばその実装になっている背景や延々とレガシーコードを読むとか
• 持っているスキルを使って最速でタスクをこなしていく、有識者から仕様を聞き回りレガシーコードを読み業務仕様を追って行った1年
• 業務知識が増えたことによって視野が広がった
• チームの人が入れ替わりまくって辛かった、また新しい方が入ってきてチームとして人が増えていろいろやっていけそうで嬉しい
• 全くのエンジニア実務未経験という方を教えることになったので教える力がついた

プライベート

• VTuberドハマりした
  • にじさんじの熱気がやばい
  • 時間がいくらあっても足りないので配信にはいかず切り抜き動画とか見てる
• 自分もVTuber的なことしてみた
  • いがにん - YouTube
  • とはいえ時間がうまく作れず全然活動できていない
• キャンプが定期化してきてクオリティが上がってきて楽しい＆飯美味い
  • これは2018年からだけど一緒にいく人数も増えてわいわいやれて楽しい
  • 昭和の森フォレストビレッジ
  • 四尾連湖
  • キャンプ・ベアード
• 旅館も行った
  • 伊香保温泉 お宿玉樹
  • 伊豆・北川温泉 望水
• Spotify契約したのとWF-1000XM3買ったことによりQOL爆上がり
  • ワイヤレスノイズキャンセリングステレオヘッドセットは最高
• 寿司食べ放題行く会の発足
• ビルコンの友達の出典のお手伝いした
  • あまり勉強会いかなくなってきたんだけどそこで触れるコミュニティっていいものだなと思った
• Roppongi.vueで登壇（これは会場提供してたから仕事か？）

今年はアウトプットが弱かった年なのでいろいろやっていきたいな、仕事、プライベートどちらも

熱海・伊豆旅行記 2/3 望水編 絶景の海!日の出が見れる旅館！

熱海・伊豆旅行記 2つ目です。

伊豆北川駅

MOA美術館を見た後は熱海駅に戻りそこから伊豆北川駅まで電車移動。
1時間電車に揺られて到着。
そこは無人駅でした。

 駅からの眺め

駅では猫がお出迎え

ただしエサ厳禁

駅から旅館までは10分ほど歩きます。
こんな道を下って、海岸線を歩きます。

弧を描いた海岸線なので端からでも目的地の宿が見えてる

望水到着

やっとこさ到着。
この時点で4時くらいになってたと思う。

宿泊する宿は望水というお宿です。

www.bousui.com

でかい・・・思った以上に。
8階建てなので旅館？にしてはホテルっぽい感じもある。
海岸線の方が入り口かと思ったらこっちは裏口で、正面入り口はどうやら8階らしい。

部屋、ラウンジ

一度8階のラウンジに通された後チェックインを済ませたらお部屋に通されました。
今回は7階のお部屋。
確か一番お安いスタンダードなお部屋だったと思う。

お部屋に通された後はお茶と菓子

温泉

お部屋で少し休んだ後は温泉へ

温泉を上がると湯上りサロンというものがあり、そこでところてんが食べられます

めちゃくちゃ美味しかったところてん
行くことがあれば絶対食べてほしい
わさび味が絶妙にさっぱりしていて最高だった

夕食

温泉で休んだ後はお部屋食

どれもこれも美味しかった・・・！

食べた後はプライベートガゼボへ

プライベートガゼボ

望水ではプライベートガゼボという特別なお風呂が用意されています。
宿泊者には1回50分が無料でついています。
追加料金払えば回数を増やせるみたいですね。
お部屋に着いた時にいつ入るか聞かれていたので夕食の後の時間でお願いしました。
準備ができたら部屋に電話がかかってくるので向かいます。

そんなお風呂があるんだ〜くらいで向かったら凄かった。

なんだろう・・・完成された空間だった。

2019/10/22 伊豆北川温泉の旅館、望水のプライベートガゼボ pic.twitter.com/RrVOprAmKg

— 山口さんちのいがにん (@igayamaguchi) November 23, 2019

この空間で裸になって風呂入ったり、バスローブに包まれてゆったりしたりを繰り返してすごく贅沢で幸せな時間だったw

夜の散策

ガゼボを堪能した後は夜の散策に。

8階の正面入り口の看板

夜の望水

ラウンジでチョコとドリンクが振舞われたり 

1日目満喫できました。
おやすみなさい〜。

2日目は日の出から

望水の大きな特徴として絶対あげたいものが日の出コールというものがあります。
スタッフに伝えておくと日の出が出る時間に部屋に電話して起こしてくれるというもの。
この電話をしてくれることで宿から日の出を眺めることができ最高です。
雲などで隠れて日の出が見れない日はそのままお電話しないとのこと。
今回は運よく日の出が出てお電話が！

日の出が最高に綺麗で良かった！
これが一番いい体験でしたね。

2019/10/23 伊豆北川温泉の日の出 pic.twitter.com/6PdqCR9m11

— 山口さんちのいがにん (@igayamaguchi) November 23, 2019

ラウンジからの日の出

部屋から

日の出を浴びる望水

日の出を浴びる伊豆北川温泉

台風の影響で入れなかった黒根岩風呂

朝風呂

朝風呂に入った後はまた湯上りサロンで軽食（温泉は人がいたので写真は撮れなかった）

朝食

朝食ももちろん美味。

海苔が食べ放題でめっちゃ食べてしまった

チェックアウト

日も上がり切ったので部屋からは綺麗な海が見えてまたいい景色

2019/10/23 伊豆北川温泉の旅館、望水からの眺め pic.twitter.com/MVSSA4ZVSh

— 山口さんちのいがにん (@igayamaguchi) November 23, 2019

ラウンジからも綺麗

そんなこんなでチェックアウトを済ませて望水、伊豆北川温泉を後に。

2019/10/23 伊豆北川温泉の海岸とカモメ pic.twitter.com/zT3ypiEg3i

— 山口さんちのいがにん (@igayamaguchi) November 23, 2019

 充実しすぎ、写真多すぎでめちゃくちゃ長くなってしまった・・・これは是非足を運んで欲しい、また行きたい宿でした。

次は城ヶ崎海岸へ。