taisablog.com

taisa's engineer blog

GORMでよく使うSQLの書き方

GORMでよく使うSQLの書き方をメモとして残しておきます。詳細は記事最後の参考リンクの公式ドキュメントで確認できます。

CREATE

insertするだけであれば create を利用します。

db.Create(&models.User{Name: "user"})
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 11:22:00','2020-04-25 11:22:00','user',0)

UPDATE

特定のフィールドだけ更新したい場合、 Update と Updates を使います。

update

一つのフィールドだけアップデートします。

user := &models.User{Name: "user"}
db.Create(user)
db.Model(user).Update("name", "user2")
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 11:34:39','2020-04-25 11:34:39','user','')

UPDATE "users" SET "name" = 'user2', "updated_at" = '2020-04-25 11:34:39'  WHERE "users"."id" = 5

updates

updates は map を利用して複数のフィールドをアップデートします。

user := &models.User{Name: "user"}
db.Create(user)
db.Model(user).Updates(map[string]interface{}{"name": "user3", "email": "g5.taisa831@gmail.com"})
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 11:39:52','2020-04-25 11:39:52','user','')

UPDATE "users" SET "email" = 'g5.taisa831@gmail.com', "name" = 'user3', "updated_at" = '2020-04-25 11:39:52'  WHERE "users"."id" = 6

SAVE

Save は SQL を実行する際にすべてのフィールドを含みます。フィールドを指定しなくても空にはなりません。

user := &models.User{Name: "user", Email: "g5.taisa831@gmail.com"}
db.Create(user)

user.Name = "user2"
db.Save(user)
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 11:43:30','2020-04-25 11:43:30','user','g5.taisa831@gmail.com')

UPDATE "users" SET "created_at" = '2020-04-25 11:43:30', "updated_at" = '2020-04-25 11:43:30', "name" = 'user2', "email" = 'g5.taisa831@gmail.com'  WHERE "users"."id" = 8 

Assign FirstOrCreate

create or update を利用するには AssignFirstOrCreate を利用します。

レコードが存在しない場合

user := &models.User{}
db.Where("name = ?", "non user").Assign(models.User{Name: "user"}).FirstOrCreate(&user)
# 実行SQL
SELECT * FROM "users"  WHERE (name = 'non user') ORDER BY "users"."id" ASC LIMIT 1

INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 12:04:24','2020-04-25 12:04:24','user','') 

レコードが存在する場合

user := &models.User{}
db.Where("name = ?", "user").Assign(models.User{Name: "user2"}).FirstOrCreate(user)
# 実行SQL
SELECT * FROM "users"  WHERE (name = 'user') ORDER BY "users"."id" ASC LIMIT 1

UPDATE "users" SET "name" = 'user2', "updated_at" = '2020-04-25 12:05:45'  WHERE "users"."id" = 3 AND ((name = 'user'))

READ

1レコードだけ取得するにはFirstを利用します。

user := &models.User{Name: "user", Email: "g5.taisa831@gmail.com"}
db.Create(user)

db.First(user, "name = ?", "user")
db.Where("name = ?", "user").First(user)
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 11:48:25','2020-04-25 11:48:25','user','g5.taisa831@gmail.com') 

SELECT * FROM "users"  WHERE "users"."id" = 10 AND ((name = 'user')) ORDER BY "users"."id" ASC LIMIT 1

SELECT * FROM "users"  WHERE "users"."id" = 10 AND ((name = 'user')) ORDER BY "users"."id" ASC LIMIT 1 

JOIN

joinのサンプルは下記となります。

user := &models.User{Name: "user", Email: "g5.taisa831@gmail.com"}
db.Create(user)

post := &models.Post{
        Post:      "post",
        UserId:    user.ID,
    }
db.Create(post)

var users []models.User
db.Table("users").Select("users.*, posts.*").Joins("inner join posts on users.id = posts.user_id").Find(&users)
# 実行SQL
INSERT INTO "users" ("created_at","updated_at","name","email") VALUES ('2020-04-25 12:26:39','2020-04-25 12:26:39','user','g5.taisa831@gmail.com')

INSERT INTO "posts" ("created_at","updated_at","user_id","post") VALUES ('2020-04-25 12:26:39','2020-04-25 12:26:39',14,'post')

SELECT users.*, posts.* FROM "users" inner join posts on users.id = posts.user_id 

> user

PRELOAD

UserモデルにPostsを追加すると PRELOAD が利用できます。PRELOAD を利用すると JOIN せずに関連レコードを取得することができます。下記のようなJSONを返したいときに便利です。

users {
    "id": "1",
        "name": "user",
    "posts" [
        {},
        {},
        {},
        {},
    ]
}
type User struct {
    ID        uint `gorm:"primary_key"`
    CreatedAt time.Time
    UpdatedAt time.Time
    Posts     Post
    Name      string
    Email     string
}

type Post struct {
    ID        uint `gorm:"primary_key"`
    CreatedAt time.Time
    UpdatedAt time.Time
    UserId    uint
    Post      string
}

user := &models.User{Name: "user", Email: "g5.taisa831@gmail.com"}
db.Preload("Posts").Find(&user)
# 実行SQL
SELECT * FROM "users"  WHERE "users"."id" = 16 

SELECT * FROM "posts"  WHERE ("user_id" IN (16))

参考