Xormで複雑なSQLを実行する
Yu Takahashi4 min read以前、基本的な使い方をまとめた記事を書きましたが、もう少し複雑なことをしたくなって色々試したので、まとめておきます。
GolangのORM、Xormの使い方ざっくりまとめ
最近、GolangのORMとしてXormを使っています。GoのORMといえばGormが主流で、調べてもXormに関する記事が少なかったので、備忘録としてまとめてみました。 自分が業務で使用している機能のみをまとめたので、さらに詳しく知りたい場合は公式ドキュメントを参照ください。 環境 * Go : 1.13.1 * Xorm : 0.7.9 * PostgreSQL : 11.6 使用するテーブルcreate table users( id BIGSERIAL, first_name VARCHAR(20) NOT NULL, last_nameVARCHAR(20) NOT …
Yu Takahashi
環境
- Go : 1.15.8
- Xorm : 1.0.7
- PostgreSQL : 11.6
使用するテーブルは前回と同じです。
JOIN にエイリアスをつける
サブクエリをJOINするとき、エイリアスをつけなければ実行できないケースがあります。しかし、Join メソッドの引数にはエイリアスをセットするパラメータがありません。
xorm.io/builder を使うと、追加できます。
| subSQL, subArgs, err := builder. | |
| Select("*"). | |
| From("users"). | |
| Where(builder.Like{"last_name", "テスト"}). | |
| ToSQL() | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| query := builder. | |
| Select("friends.*"). | |
| From("friends"). | |
| InnerJoin(fmt.Sprintf("(%s) AS sub_1", subSQL), | |
| builder.Expr("friends.user_id = sub_1.id", subArgs...), | |
| ) | |
| sql, args, err := query.ToSQL() | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| log.Println(sql) |
SELECT
friends.*
FROM
friends
INNER JOIN
(
SELECT
*
FROM
users
WHERE
last_name LIKE $1
) AS sub_1
ON friends.user_id = sub_1.id13,14行目のように fmt.Sprintf でエイリアスを追加し、引数はbuilder.Expr で後から追加しています。若干無理やりですが、これで動きます。
（ちなみに上の例ではエイリアスをつけなくても実行できます。サブクエリがもっと複雑になるとエイリアスが必要になったりします。）
任意のSQLを実行し、結果をバインドする
上の例で構築したSQLを実行するためには以下のようにします。
| friends := make([]friend, 0) | |
| err := engine.SQL(sql, args...).Find(&friends) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| log.Println(friends) | |
| rows, err := engine.SQL(sql, args...).Rows(&friend{}) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| friends := make([]friend, 0) | |
| for rows.Next() { | |
| dto := friend{} | |
| err := rows.Scan(&dto) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| friends = append(friends, dto) | |
| } | |
| log.Println(friends) |
どちらでもできますが、基本的に1番目のほうで問題ないと思います。
WITH を使う
WITH はXormで用意されてないようなので、自分で構築する必要があります。
| withSQL, withArgs, err := builder. | |
| Select("*"). | |
| From("users"). | |
| Where(builder.Like{"last_name", "テスト"}). | |
| ToSQL() | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| const alias = "sub_2" | |
| mainSQL, mainArgs, err := builder. | |
| Select("*"). | |
| From("friends"). | |
| Where(builder.Expr(fmt.Sprintf("user_id IN (SELECT id from %s)", alias))). | |
| ToSQL() | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| sql := fmt.Sprintf("WITH %s AS (%s) %s", alias, withSQL, mainSQL) | |
| args := append(withArgs, mainArgs...) | |
| friends := make([]friend, 0) | |
| err = engine.SQL(sql, args...).Find(&friends) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| log.Println(friends) |
WITH sub_2 AS(
SELECT
*
FROM
users
WHERE
last_name LIKE $1
)
SELECT
*
FROM
friends
WHERE
user_id IN(
SELECT
id
from
sub_2
)BulkInsert した結果のIDを取得する
前回の記事でも書きましたが、BulkInsert するとIDを自動で構造体にセットしてくれません。自分でSQLを構築すると取得できます。
| // insert対象のデータを生成 | |
| users := make([]user, 3) | |
| for i = 0; i < 3; i++ { | |
| users[i] = user{ | |
| FirstName: fmt.Sprintf("太郎+%v", i), | |
| LastName: fmt.Sprintf("テスト+%v", i), | |
| } | |
| } | |
| valueStrings := make([]string, len(users)) | |
| args := make([]interface{}, 0, len(users)*5) | |
| for i, u := range users { | |
| valueStrings[i] = "(?,?,?,?,?,now(),now())" | |
| args = append(args, u.FirstName, u.LastName, u.FirstNameKana, u.LastNameKana, 1) | |
| } | |
| valueString := strings.Join(valueStrings, ",") | |
| sql := fmt.Sprintf("INSERT INTO users (first_name, last_name, first_name_kana, last_name_kana, version, created_at, updated_at) VALUES %s RETURNING id", valueString) | |
| ids := make([]string, 0) | |
| err := engine.SQL(sql, args...).Find(&ids) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| log.Println(ids) |
INSERT INTO users(
first_name,
last_name,
first_name_kana,
last_name_kana,
version,
created_at,
updated_at
)
VALUES(
$1,
$2,
$3,
$4,
$5,
now(),
now()
),
(
$6,
$7,
$8,
$9,
$10,
now(),
now()
),
(
$11,
$12,
$13,
$14,
$15,
now(),
now()
) RETURNING idInsert なのにFind メソッドを使っているのが非常に違和感ありますが、一応動きます。
複数のカラムを返すこともできます。
| users := make([]user, 3) | |
| for i = 0; i < 3; i++ { | |
| users[i] = user{ | |
| FirstName: fmt.Sprintf("太郎+%v", i), | |
| LastName: fmt.Sprintf("テスト+%v", i), | |
| } | |
| } | |
| valueStrings := make([]string, len(users)) | |
| args := make([]interface{}, 0, len(users)*5) | |
| for i, u := range users { | |
| valueStrings[i] = "(?,?,?,?,?,now(),now())" | |
| args = append(args, u.FirstName, u.LastName, u.FirstNameKana, u.LastNameKana, 1) | |
| } | |
| valueString := strings.Join(valueStrings, ",") | |
| sql := fmt.Sprintf("INSERT INTO users (first_name, last_name, first_name_kana, last_name_kana, version, created_at, updated_at) VALUES %s RETURNING id, first_name", valueString) | |
| createdUsers := make([]user, 0) | |
| err := engine.SQL(sql, args...).Find(&createdUsers) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| log.Println(createdUsers) |
INSERT INTO users(
first_name,
last_name,
first_name_kana,
last_name_kana,
version,
created_at,
updated_at
)
VALUES(
$1,
$2,
$3,
$4,
$5,
now(),
now()
),
(
$6,
$7,
$8,
$9,
$10,
now(),
now()
),
(
$11,
$12,
$13,
$14,
$15,
now(),
now()
) RETURNING id,
first_name以下のように、QueryString メソッドでも同じことできます。Find を使うよりは違和感ないですが、少しコードが長くなります。余計なバインド処理がなくなるので、パフォーマンスは良いかもしれません。
| results, err := engine.QueryString(append([]interface{}{sql}, args...)...) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| ids := make([]int, len(results)) | |
| firstNames := make([]string, len(results)) | |
| for i, row := range results { | |
| idString, ok := row["id"] | |
| if !ok { | |
| log.Println("error") | |
| return | |
| } | |
| id, err := strconv.Atoi(idString) | |
| if err != nil { | |
| log.Println(err) | |
| return | |
| } | |
| ids[i] = id | |
| firstName, ok := row["first_name"] | |
| if !ok { | |
| log.Println("error") | |
| return | |
| } | |
| firstNames[i] = firstName | |
| } | |
| log.Println(ids) | |
| log.Println(firstNames) |
以上です。結構柔軟にSQL実行できるみたいです。