この記事は東京学芸大学 櫨山研究室 Advent Calendar 2020の7日目の記事になります.
初めての記事投稿で分かりづらいかもしれませんが, 暖かい目で見てほしいです.
変なところはコメントください！
#はじめに
Goには素晴らしきORM(おーあーるまっぱー, SQLを知らなくてもDBとのやりとりができる！ってやつ)であるGORMが存在していて, 大変お世話になっています.
Association周りが全然わかっていないので, Debug()モードで発行されるSQLを確認していきます.
#概要
gorm公式のAssociationに記述されているメソッドたちに, Debug()メソッドを用いて発行されるSQL文を確認していく.
今回は1対多のケースのみでやっています.
##用意したモデル, テーブル
####モデル
package models
type Writer struct {
ID int
Name string
Articles []Article `gorm:"ForeignKey:WriterID"`
}
type Article struct {
ID int
Title string
WriterID int `gorm:"column:writer_id"`
}
####テーブル
CREATE TABLE `writers`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
CREATE TABLE `articles`(
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`title` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`writer_id` int(10) unsigned,
`created_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
`updated_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
FOREIGN KEY (`writer_id`) REFERENCES `writers`(`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
関連は,
writer 1 --write-- * article
となっています.
##Create
####実行するコード
newWriter := models.Writer{
Name: "new name",
Articles: []models.Article{
{
Title: "新しい記事の形",
},
{
Title: "新版！元気になる高笑い",
},
},
}
db.Debug().Create(&newWriter)
fmt.Printf("result: %+v\n", newWriter)
####実行結果
[2020-12-06 16:59:22] [3.64ms] INSERT INTO `writers` (`name`) VALUES ('new name')
[1 rows affected or returned ]
[2020-12-06 16:59:22] [6.34ms] INSERT INTO `articles` (`title`,`writer_id`) VALUES ('新しい記事の形',8)
[1 rows affected or returned ]
[2020-12-06 16:59:22] [7.60ms] INSERT INTO `articles` (`title`,`writer_id`) VALUES ('新版！元気になる高笑い',8)
[1 rows affected or returned ]
result: {ID:8 Name:new name Articles:[{ID:12 Title:新しい記事の形 WriterID:8} {ID:13 Title:新版！元気になる高笑い WriterID:8}] Likes:[]}
DBに登録させていない記事を構造体に持たせると新しい子レコードを作成してくれる.
Omit()を用いれば, Articlesを保存しなくて済むそう.
newWriter := models.Writer{
Name: "new name2",
Articles: []models.Article{
{
Title: "本を書いてみる",
},
{
Title: "新作の本B",
},
},
}
db.Debug().Omit("Articles").Create(&newWriter)
fmt.Printf("result: %+v\n", newWriter)
####実行結果
2020-12-06 22:30:45] [3.75ms] INSERT INTO `writers` (`name`) VALUES ('new name2')
[1 rows affected or returned ]
result: {ID:8 Name:new name2 Articles:[{ID:0 Title:本を書いてみる WriterID:0} {ID:0 Title:新作の本B WriterID:0}] Likes:[]}
新しいArticleが保存されていない！
Association Find
子要素を持ってこれるらしい
writer := models.Writer{
ID: 1,
}
var articles []models.Article
db.Debug().Model(&writer).Association("Articles").Find(&articles)
####実行結果
[2020-12-06 22:47:20] [3.95ms] SELECT * FROM `articles` WHERE (`writer_id` = 1)
[1 rows affected or returned ]
Append Association
####実行するコード
子要素を追加することができる.
下記のコードの場合, IDが１番のwriterにarticle10という名前のarticleが追加される
writer := models.Writer{
ID: 1,
}
articles := []models.Article{
{
Title: "article10",
},
}
db.Debug().Model(&writer).Association("Articles").Append(articles)
実行結果
[2020-12-06 23:13:28] [3.32ms] INSERT INTO `articles` (`title`,`writer_id`) VALUES ('article10',1)
[1 rows affected or returned ]
[2020-12-06 23:13:28] [5.35ms] SELECT * FROM `writers` WHERE `writers`.`id` = 1 ORDER BY `writers`.`id` ASC LIMIT 1
[1 rows affected or returned ]
Replace Associations
実行するコード
writer := models.Writer{
ID: 1,
}
articles := []models.Article{
{
ID: 3,
Title: "article10",
},
{
ID: 4,
Title: "article1010",
},
}
db.Debug().Model(&writer).Association("Articles").Replace(&articles)
実行結果
[2020-12-06 23:48:38] [4.24ms] UPDATE `articles` SET `title` = 'article10', `writer_id` = 1 WHERE `articles`.`id` = 3
[1 rows affected or returned ]
[2020-12-06 23:48:38] [7.74ms] SELECT * FROM `writers` WHERE `writers`.`id` = 1 ORDER BY `writers`.`id` ASC LIMIT 1
[1 rows affected or returned ]
[2020-12-06 23:48:38] [4.29ms] UPDATE `articles` SET `title` = 'article10', `writer_id` = 1 WHERE `articles`.`id` = 3
[0 rows affected or returned ]
[2020-12-06 23:48:38] [6.09ms] SELECT * FROM `articles` WHERE `articles`.`id` = 3 ORDER BY `articles`.`id` ASC LIMIT 1
[1 rows affected or returned ]
[2020-12-06 23:48:38] [10.70ms] UPDATE `articles` SET `title` = 'article1010', `writer_id` = 1 WHERE `articles`.`id` = 4
[0 rows affected or returned ]
[2020-12-06 23:48:38] [10.22ms] SELECT * FROM `articles` WHERE `articles`.`id` = 4 ORDER BY `articles`.`id` ASC LIMIT 1
[0 rows affected or returned ]
[2020-12-06 23:48:38] [23.08ms] INSERT INTO `articles` (`id`,`title`,`writer_id`) VALUES (4,'article1010',1)
[1 rows affected or returned ]
[2020-12-06 23:48:38] [3.73ms] SELECT * FROM `writers` WHERE `writers`.`id` = 1 ORDER BY `writers`.`id` ASC LIMIT 1
[1 rows affected or returned ]
[2020-12-06 23:48:38] [4.34ms] UPDATE `articles` SET `writer_id` = NULL WHERE (`id` NOT IN (3,4)) AND (`writer_id` = 1)
[0 rows affected or returned ]
Replaceするときは, 新しく子要素に追加したもの以外は, 外部キーとなるカラムの値がNULLになるっぽい.
Delete Association
####実行するコード
writer := models.Writer{
ID: 1,
}
articles := []models.Article{
{
ID: 3,
Title: "article10",
},
{
ID: 4,
Title: "article1010",
},
}
db.Debug().Model(&writer).Association("Articles").Delete(&articles)
####実行結果
[2020-12-06 23:56:37] [4.91ms] UPDATE `articles` SET `writer_id` = NULL WHERE (`writer_id` IN (1)) AND (`id` IN (3,4))
[0 rows affected or returned ]
指定した子要素のarticleのwriter_id(外部キー)がNULLになるようにSQLが発行されている.
Clear Association
####実行するコード
writer := models.Writer{
ID: 1,
}
db.Debug().Model(&writer).Association("Articles").Clear()
実行結果
[2020-12-07 00:01:36] [4.39ms] UPDATE `articles` SET `writer_id` = NULL WHERE (`writer_id` = 1)
[0 rows affected or returned ]
Delete Associationよりも強力で子要素を全部消してるらしい.
##おわりに
Associationモードで発行されるSQL文をまとめてみました.
1対多でしか検証ができていないので, いつかMany2Manyのケースでも試してみたいです.