![[golang] SQLBoiler のいろは (2) Query 応用編](http://35.200.14.143/wp-content/uploads/2020/04/sqlboiler-730x160.png)
こんばんは、七色メガネです。
前回に引き続き、golang の ORM ツールである SQLBoiler について調査・学習していきたいと思います。
前回は SQLBoiler の設定と基本 CRUD について取り扱ったので、今回は応用編として、各種 Query の実行方法についてまとめていきます。
SQLBoiler ってなに？設定はどうやるの？
こちらは前回の記事で扱いましたので、よろしければ下記の記事をご覧ください。
各種 Query の実践
データ準備
ではデータ準備から初めていきます。以降は DDL から model を生成できる、という前提に立って進めていきます。もしその方法がわからない場合、前回の記事をご参照ください。
DDL
前回同様、以下の DDL を使用します。
・User : 他テーブルから参照される親テーブル。
・Division : 必ず User を親に持つ子テーブル。外部キー制約で User と紐づく。
・Branch : 任意で User を親に持つ子テーブル。制約はなく、user_id は nullable。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
create table user( user_id int not null auto_increment, name varchar(10) not null, age int not null, primary key(user_id) ); create table division( division_id int auto_increment, name varchar(10) not null, user_id int not null, primary key(division_id), constraint fk_user_div foreign key(user_id) references user (user_id) on delete no action on update no action ); create table branch( branch_id int auto_increment, name varchar(10) not null, user_id int null, primary key(branch_id) ); |
Division は 必ず User との relation を持つように作っていますから、Inner Join の test で使用します。
Branch は任意で User との relation を持つように作っていますから、Outer Join の test で使用します。
データ
DDL を実行し、model を生成したら、model を使ってデータを用意しましょう。
データを用意するためのコードは次の通りです。とりあえず今は、こんなデータを用意しているのだなという雰囲気だけ読み取ってもらえれば大丈夫です。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
// prepare_data is a function to prepare test data. func prepare_data() error { if err := (&models.User{UserID: 1, Name: "Ren", Age: 27}).Insert(context.Background(), DB, boil.Infer()); err != nil { return err } if err := (&models.User{UserID: 2, Name: "Kim", Age: 25}).Insert(context.Background(), DB, boil.Infer()); err != nil { return err } if err := (&models.User{UserID: 3, Name: "Sasa", Age: 27}).Insert(context.Background(), DB, boil.Infer()); err != nil { return err } if err := (&models.Division{DivisionID: 1, Name: "Akita", UserID: 1}).Insert(context.Background(), DB, boil.Infer()); err != nil { return err } if err := (&models.Branch{BranchID: 1, Name: "Oodate", UserID: null.IntFrom(1)}).Insert(context.Background(), DB, boil.Infer()); err != nil { return err } return nil } |
Query を指定し、Model に用意された Finisher で 実行する
まず最初は、生成された model に receiver として用意されている finisher を使用して Query を実行する方法です。
Where の指定が最低限で済み、また Query 実行結果は model に用意されている struct として返却されるので、一番簡単に条件付き Query を発行できます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
// built_query is a function to execute query with userQuery receiver. func built_query() error { result, err := models.Users(qm.Where("age = ?", 27)).All(context.Background(), DB) if err != nil { return err } fmt.Println("--- built query ---") for _, user := range result { fmt.Printf("result : \n user_id: %d\n name: %s\n age: %d\n", user.UserID, user.Name, user.Age) } fmt.Println() return nil } |
|
1 2 3 4 5 6 7 8 9 |
--- built query --- result : user_id: 1 name: Ren age: 27 result : user_id: 3 name: Sasa age: 27 |
今回は「条件に一致する全てのレコードを取得する」という効果を持った All という finisher を使用しましたが、他にもレコードを一つだけ取得する One や条件を満たすレコードの数を返却する Count といったものも存在するので、基本的な Query 実行はこれで事足りることと思います。
デメリットとしては、レコードを取得しようとした時、返却される struct が model で用意されている struct の形になってしまうということです。自分で struct を定義してその形でデータを取得しようと思った時には、この方法は使えません。
Query を組み立て、結果を指定した struct に Bind する
用意された qm キーワードを使用して、もっと柔軟に Query を組み立て、実行することもできます。
|
1 2 3 |
type UserSliceModel struct { models.UserSlice } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
// custom_query is a function to execute query with Query receiver. func custom_query() error { var result = new(UserSliceModel) var queries []qm.QueryMod { queries = append(queries, qm.Select("*")) queries = append(queries, qm.From("User")) queries = append(queries, qm.Where("age = ?", 27)) } err := models.NewQuery(queries...).Bind(context.Background(), DB, &result.UserSlice) if err == nil { fmt.Println("--- custom query ---") for _, user := range result.UserSlice { fmt.Printf("result : \n user_id: %d\n name: %s\n age: %d\n", user.UserID, user.Name, user.Age) } } fmt.Println() return err } |
|
1 2 3 4 5 6 7 8 9 |
--- custom query --- result : user_id: 1 name: Ren age: 27 result : user_id: 3 name: Sasa age: 27 |
このように qm キーワードを作成し、Query を部分的に定義・組み立てたうえで実行することもできます。結果は先ほどの例と全く同じです。
メリットとしてはより柔軟な Query 発行ができるということです。
また今回使用している finisher の Bind は User の model に用意されたものではなく汎用的なも関数であり、Query の実行結果を引数に渡した struct に bind してくれます。
ここでは model で定義されている struct をラップしただけの struct を渡しているだけなのでメリットを感じませんが、後々、この機能が必須になる場面が出てきます。
デメリットとしては、最初に紹介したパターンよりも処理が煩雑であるということでしょうか。特に query の組み立ては、複雑になればなるほど、可読性が落ちていくでしょう。
生 Query を記述し、結果を指定した struct に Bind する
上の２例では where 句の指定などを qm キーワードで行なっていましたが、通常の SQL のように、そのまま直接 Query を記述し、実行することもできます。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 |
// raw_query is a function to execute query with raw query. func raw_query() error { var result = new(UserSliceModel) var query = ` SELECT user_id, name, age FROM user WHERE age = 25 ` err := models.NewQuery(qm.SQL(query)).Bind(context.Background(), DB, &result.UserSlice) if err == nil { fmt.Println("--- raw query ---") for _, user := range result.UserSlice { fmt.Printf("result : \n user_id: %d\n name: %s\n age: %d\n", user.UserID, user.Name, user.Age) } } fmt.Println() return err } |
|
1 2 3 4 5 |
--- raw query --- result : user_id: 2 name: Kim age: 25 |
メリットはあらゆる形の query を組み立てられるということですが、もはや ORM の良さをかなぐり捨てているので、本来であればあまり使うべき手法ではありません。
しかし、特定の場合にはこの方法に頼らねければいけないことがあるので紹介しておきます。
特定の場合については、後述します。
Query を組み立て、結果を original な struct に Bind する
ここでの紹介は、Query 組み立てパターンと同じです。ただ bind する先の struct に、自分で定義した struct を使用してみます。
|
1 2 3 4 5 6 7 8 |
type UserCustomModel struct { UserID int `boil:"user_id"` Name string `boil:"name"` } type UserCustomSliceModel struct { USlice []*UserCustomModel } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
// custom_struct is a function to execute query with Query receiver and bind result to custom struct. func custom_struct() error { var result = new(UserCustomSliceModel) var queries []qm.QueryMod { queries = append(queries, qm.Select("user_id, name")) queries = append(queries, qm.From("User")) queries = append(queries, qm.Where("user_id = ?", 3)) } err := models.NewQuery(queries...).Bind(context.Background(), DB, &result.USlice) if err == nil { fmt.Println("--- custom struct ---") for _, user := range result.USlice { fmt.Printf("result : \n user_id: %d\n name: %s\n", user.UserID, user.Name) } } fmt.Println() return err } |
|
1 2 3 4 |
--- custom struct --- result : user_id: 3 name: Sasa |
User テーブルは user_id と name と age を持っていますが、ここでは user_id と name だけを持った struct を定義し、それに bind しているため、取得結果が今までと異なっています。
この例ではいまいちメリットを感じませんが、次で紹介するように Join を行う時などは、model に用意されている単一テーブルの情報しか持たない struct では使用に堪えませんから、このように自分で struct を定義してそこへ bind するという必要性が生まれます。
注意点としては、Select するカラム名と struct で指定した boil:" xxx " の記述が一致しない場合、bind が上手く行われないということです。
またカラム名の重複がある場合にも、bind が正常に機能しません。重複がある場合には、AS キーワードを使うなどして、各カラム情報を明確に区別する必要があります。
Inner Join を行う
Inner Join は、今までに学んだことの組み合わせです。qm キーワードとして Inner Join が用意されているのでそれを使用し、Join した後の結果を bind するための struct を自分で用意し、後は実行するだけです。
|
1 2 3 4 5 6 7 8 9 10 |
type UserAndDivision struct { UserID int `boil:"user_id"` UserName string `boil:"user_name"` DivisionID int `boil:"division_id"` DivisionName string `boil:"division_name"` } type UserAndDivisionSliceModel struct { UDSlice []*UserAndDivision } |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
// inner_join is a function to execute inner join query with Query receiver. // When use inner join, you need prepare custom struct. func inner_join() error { var result = new(UserAndDivisionSliceModel) var queries []qm.QueryMod { queries = append(queries, qm.Select("U.user_id AS user_id, U.name AS user_name, D.division_id AS division_id, D.name AS division_name")) queries = append(queries, qm.From("User AS U")) queries = append(queries, qm.InnerJoin("Division AS D ON U.user_id = D.user_id")) } err := models.NewQuery(queries...).Bind(context.Background(), DB, &result.UDSlice) if err == nil { fmt.Println("--- inner join ---") for _, ud := range result.UDSlice { fmt.Printf("result : \n user_id: %d\n user_name: %s\n division_id: %d\n division_name: %s\n", ud.UserID, ud.UserName, ud.DivisionID, ud.DivisionName) } } fmt.Println() return err } |
|
1 2 3 4 5 6 |
--- inner join --- result : user_id: 1 user_name: Ren division_id: 1 division_name: Akita |
気をつける点としては、上の例でも述べてしまったのですが、カラム名の区別です。
今回 User テーブルにも Division テーブルにも name というカラムがありそれを取得しようとしていますが、もし struct でこの２つの name を区別するように記述していなければ、bind 結果は期待するものになりません。
今回は struct にて、
・UserName string boil:"user_name"
・DivisionName string boil:"division_name"
と区別を行い、それぞれに適切に bind されるよう、Select 句 で AS による別名を指定しています。
Outer Join を行う
最後に Outer Join です。実は SQLBoiler は標準で Outer Join をサポートしていません。qm キーワードもありません。従って、ユーザー側の工夫で Outer Join を実現するしかありません。
まずは struct です。自分で専用の struct を用意しなければいけないのは今まで同じですが、定義する際、null を許容するように記述することが重要です。
|
1 2 3 4 5 6 7 8 9 10 |
type UserAndBranch struct { UserID int `boil:"user_id"` UserName string `boil:"user_name"` BranchID null.Int `boil:"branch_id"` BranchName null.String `boil:"branch_name"` } type UserAndBranchSliceModel struct { UBSlice []*UserAndBranch } |
今回は User をベースにした Branch との Left Outer Join を想定しているので、 Branch 側の結果が取得されない可能性があります。ので、Branch 側の結果を bind するフィールドは、null 許容型で定義しておく必要があります。これを忘れると、実行時に( Branch 側に一致するレコードがない場合 )エラーになります。
次は Query の設定ですが、qm キーワードがない以上、もう自分で生 Query を書くしかありません。先に述べていた生 Query を使わなければいけない場面というのは、ここです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
// outer_join is a function to execute inner join query with raw query. // When use outer join, you need prepare custom struct which is constituted nullable variable. func outer_join() error { var result = new(UserAndBranchSliceModel) var outer_join_query = ` SELECT U.user_id AS user_id, U.name AS user_name, B.branch_id AS branch_id, B.name AS branch_name FROM user AS U LEFT OUTER JOIN Branch AS B ON U.user_id = B.user_id` err := models.NewQuery(qm.SQL(outer_join_query)).Bind(context.Background(), DB, &result.UBSlice) if err == nil { fmt.Println("--- outer join ---") for _, ub := range result.UBSlice { fmt.Printf("result : \n user_id: %d\n user_name: %s\n branch_id: %d\n branch_name: %s\n", ub.UserID, ub.UserName, ub.BranchID.Int, ub.BranchName.String) } } fmt.Println() return err } |
結果は次の通りです。最初のデータは Branch が存在しているので正常に取得され、以降のデータは Branch が存在しないので空のデータで bind できています。(int はデフォルトが 0 なので 0 に落ちていますが)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
--- outer join --- result : user_id: 1 user_name: Ren branch_id: 1 branch_name: Oodate result : user_id: 2 user_name: Kim branch_id: 0 branch_name: result : user_id: 3 user_name: Sasa branch_id: 0 branch_name: |
まとめ
・qm キーワードを使用して、Query の条件句を指定できる。(Outer Join 以外)
・model に用意された finisher を用意して Query を実行できる。
・bind という finisher を使用して自分で定義した struct に Query 実行結果を bind できる。
・生 SQL を実行することができる。
・サポートされていないが、Outer Join も使用することができる。
なんでも出来ますね。便利便利。
では今回はここまでです。
もうちょっと知見が溜まったら、次回はもっと複雑な Query 実行について触れていきたいですね。ネタが無いので、単発紹介になるかもしれませんが。
ここまでご覧いただき、ありがとうございました！
参考
https://github.com/volatiletech/sqlboiler
今回使用した src
https://github.com/NanairoMegane/sqlboiler_test
おすすめ書籍
Google Cloud Platform エンタープライズ設計ガイド
Google Cloud Platform 実践Webアプリ開発 ストーリーで学ぶGoogle App Engine
プログラマのためのGoogle Cloud Platform入門 サービスの全体像からクラウドネイティブアプリケーション構築まで
![[ golang ] Google Drive に golang でアクセスする](http://35.200.14.143/wp-content/uploads/2020/04/google-drive.jpg)
![[ golang ] GO言語で認証機能つきのチャットアプリを作ってみた。](http://35.200.14.143/wp-content/uploads/2019/03/onsei_ninshiki_computer.png)
![[ golang ] GO言語でgomniauth を使用して認証機能を実装してみる。](http://35.200.14.143/wp-content/uploads/2019/03/camera_kao_ninshiki.png)
![[ golang ] GO言語でCookieを操作してみる。](http://35.200.14.143/wp-content/uploads/2019/03/character_cookie.png)