SQLでみるgormのAssociation


この記事は[東京学芸大学 櫨山研究室 Advent Calendar 2020](https://qiita.com/advent-calendar/2020/hazelab)の7日目の記事になります.

初めての記事投稿で分かりづらいかもしれませんが, 暖かい目で見てほしいです. 
変なところはコメントください！




#はじめに

Goには素晴らしきORM(おーあーるまっぱー, SQLを知らなくてもDBとのやりとりができる！ってやつ)であるGORMが存在していて, 大変お世話になっています. 

Association周りが全然わかっていないので, Debug()モードで発行されるSQLを確認していきます. 


#概要
[gorm公式のAssociation](https://gorm.io/ja_JP/docs/associations.html)に記述されているメソッドたちに, Debug()メソッドを用いて発行されるSQL文を確認していく.
今回は1対多のケースのみでやっています.

##用意したモデル, テーブル

####モデル

```go
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"`
}
```

####テーブル
```sql

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

####実行するコード
```go
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を保存しなくて済むそう.



```go
	newWriter := models.Writer{
		Name: "new name2",
		Articles: []models.Article{
			{
				Title: "本を書いてみる",
			},
			{
				Title: "新作の本B",
			},
		},
	}
	db.Debug().Omit("Articles").Create(&newWriter)
	fmt.Printf("result: %+v\n", newWriter)
```

####実行結果
```terminal
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

子要素を持ってこれるらしい

```go

	writer := models.Writer{
		ID: 1,
	}
	var articles []models.Article
	db.Debug().Model(&writer).Association("Articles").Find(&articles)
```

####実行結果

```terminal
[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が追加される

```go
	writer := models.Writer{
		ID: 1,
	}
	articles := []models.Article{
		{
			Title: "article10",
		},
	}
	db.Debug().Model(&writer).Association("Articles").Append(articles)
```

#### 実行結果

```terminal

[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

### 実行するコード

```go
	writer := models.Writer{
		ID: 1,
	}
	articles := []models.Article{
		{
			ID:    3,
			Title: "article10",
		},
		{
			ID:    4,
			Title: "article1010",
		},
	}
	db.Debug().Model(&writer).Association("Articles").Replace(&articles)
```

#### 実行結果

```terminal

[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

####実行するコード

```go
	writer := models.Writer{
		ID: 1,
	}
	articles := []models.Article{
		{
			ID:    3,
			Title: "article10",
		},
		{
			ID:    4,
			Title: "article1010",
		},
	}
	db.Debug().Model(&writer).Association("Articles").Delete(&articles)
```

####実行結果

```terminal
[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 

####実行するコード

```go
	writer := models.Writer{
		ID: 1,
	}
	db.Debug().Model(&writer).Association("Articles").Clear()
```

#### 実行結果

```terminal
[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のケースでも試してみたいです.