search
LoginSignup
0

More than 1 year has passed since last update.

posted at

Organization

SQLでみるgormのAssociation

この記事は東京学芸大学 櫨山研究室 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が1番の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のケースでも試してみたいです.

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
0