Associations

7. Associations

7.1 Belongs To

A Belongs To association sets up a one-to-one connection with another model, such that each instance of the declaring model "belongs to" one instance of the other model

For example, if your application includes users and companies, and each user can be assigned to exactly one company, the following types represent that relationship. Notice here that, on the User object, there is both a CompanyID as well as a Company. By default, the CompanyID is implicity usesd to create a foreign key relationship between the User and Company tables, and thus must be included in the User struct in order to fill the Company inner strcut

type User struct {
	ID        uint `gorm:"primaryKey"`
	Name      string
	Age       int
	CompanyID uint
	Company   Company
}

type Company struct {
	ID   uint `gorm:"primaryKey"`
	Name string
}

7.1.1 Override Foreign Key

To define a belongs to relationship, the foreign key must exist, the default foreing key uses the owner's type name plus its primary field name.

For the above example, to define the User model that belongs to Company, the foreign key should be CompanyID by convention

GORM provides a way to customize the foreign key

type User struct {
  gorm.Model
  Name         string
  CompanyRefer int
  Company      Company `gorm:"foreignKey:CompanyRefer"`
  // use CompanyRefer as foreign key
}

type Company struct {
  ID   int
  Name string
}

7.1.2 Override References

For a belongs to relationship, GORM usually uses the owner's primary field as the foreign key's value, for the above example, it is Company's field ID

When you assign a user to a company, GORM will save the company's ID into the user's CompanyID field

You are able to change it with tag references

type User struct {
  gorm.Model
  Name      string
  CompanyID string
  Company   Company `gorm:"references:Code"` // use Code as references
}

type Company struct {
  ID   int
  Code string
  Name string
}

7.1.3 FOREIGN KEY Constraints

You can setup OnUpdate,OnDelete constraints with tag constraint, it will created when migrating with GORM,

type User struct {
  gorm.Model
  Name      string
  CompanyID int
  Company   Company `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type Company struct {
  ID   int
  Name string
}

7.2 Has One

A has one association sets up a one-to-one connection with another model, but with somewhat different semantics (and consequences). This association indicates that each instance of a model contains or prossesses one instance of another model

For example, if your application includes users and credit cards, and each user can only have one credit card

type User struct {
	ID         uint `gorm:"primaryKey"`
	Name       string
	Age        int
	CreditCard CreditCard
}

type CreditCard struct {
	ID     uint `gorm:"primaryKey"`
	Number string
	UserID uint
}

7.2.1 Override Foreign Key

For a has one relationship, a foreign key field must also exist, the owner will save the primary key of the model belongs to it into this field

The field's name is usually generated with has one model's type plus its primary key, for the above example it is UserID

When you give a credit card to the user, it will save the User's ID into its UserID field

If you want to use another field to save the relationship, you can change it with tag foreignKey

type User struct {
  gorm.Model
  CreditCard CreditCard `gorm:"foreignKey:UserName"`
  // use UserName as foreign key
}

type CreditCard struct {
  gorm.Model
  Number   string
  UserName string
}

7.2.2 Override Reference

By default, the owned entity will save the has one model’s primary key into a foreign key, you could change to save another field’s value, like using Name for the below example.

You are able to change it with tag references, e.g:

type User struct {
  gorm.Model
  Name       string     `gorm:"index"`
  CreditCard CreditCard `gorm:"foreignkey:UserName;references:name"`
}

type CreditCard struct {
  gorm.Model
  Number   string
  UserName string
}

7.2.3 Polymorphism Association

GORM supports polymorphism association for has one and has many, it will save owned entity's table name into polymorphic type's field, primary key into poltmorphic field

package main

import (
	"log"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

const (
	DBName = "polymorphism-association.db"
)

type Cat struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Toy  Toy `gorm:"polymorphic:Owner;"`
}

type Dog struct {
	ID   uint `gorm:"primaryKey"`
	Name string
	Toy  Toy `gorm:"polymorphic:Owner;"`
}

type Toy struct {
	ID        uint `gorm:"primaryKey"`
	Name      string
	OwnerID   uint
	OwnerType string
}

func main() {
	db := initializeDB()
	sqlDB, _ := db.DB()
	defer sqlDB.Close()

	createTables(db)

	// INSERT INTO cats (name) VALUES ('cat_1');
	// INSERT INTO toys (name,owner_id,owner_type) VALUES ('toy_1','1','cats');
	db.Create(&Cat{Name: "cat_1", Toy: Toy{Name: "toy_1"}})
}

func initializeDB() *gorm.DB {
	db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatalln("connect db failed: ", err.Error())
	}
	return db
}

func createTables(db *gorm.DB) {
	db.AutoMigrate(&Cat{})
	db.AutoMigrate(&Dog{})
	db.AutoMigrate(&Toy{})
}

You can change the polymorphic type value with tag polymorphicValue

type Dog struct {
  ID   int
  Name string
  Toy  Toy `gorm:"polymorphic:Owner;polymorphicValue:master"`
}

type Toy struct {
  ID        int
  Name      string
  OwnerID   int
  OwnerType string
}

db.Create(&Dog{Name: "dog1", Toy: Toy{Name: "toy1"}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","master")

7.2.4 Self-Referential Has One

type User struct {
  gorm.Model
  Name      string
  ManagerID *uint
  Manager   *User
}

7.2.5 FOREIGN KEY Constraints

You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:

type User struct {
  gorm.Model
  CreditCard CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type CreditCard struct {
  gorm.Model
  Number string
  UserID uint
}

You are also allowed to delete selected has one associations with Select when deleting, checkout Delete with Select for details

7.3 Has Many

A has many association sets up a one-to-many connection with another model, unlike has one, the owner could have zero or many instances of models

For example, if your application includes users and credit card, and each user can have many credit cards

type User struct {
	ID         uint `gorm:"primaryKey"`
	Name       string
	Age        int
	CreditCard []CreditCard
}

type CreditCard struct {
	ID     uint `gorm:"primaryKey"`
	Number string
	UserID uint
}

7.3.1 Override Foreign Key

To define a has many relationship, a foreign key must exist. The default foreign key’s name is the owner’s type name plus the name of its primary key field

For example, to define a model that belongs to User, the foreign key should be UserID.

To use another field as foreign key, you can customize it with a foreignKey tag, e.g:

type User struct {
  gorm.Model
  CreditCards []CreditCard `gorm:"foreignKey:UserRefer"`
}

type CreditCard struct {
  gorm.Model
  Number    string
  UserRefer uint
}

7.3.2 Override References

GORM usually uses the owner’s primary key as the foreign key’s value, for the above example, it is the User‘s ID,

When you assign credit cards to a user, GORM will save the user’s ID into credit cards’ UserID field.

You are able to change it with tag references, e.g:

type User struct {
  gorm.Model
  MemberNumber string
  CreditCards  []CreditCard `gorm:"foreignKey:UserNumber;references:MemberNumber"`
}

type CreditCard struct {
  gorm.Model
  Number     string
  UserNumber string
}

7.3.3 Polymorphism Association

GORM supports polymorphism association for has one and has many, it will save owned entity’s table name into polymorphic type’s field, primary key value into the polymorphic field

type Dog struct {
  ID   int
  Name string
  Toys []Toy `gorm:"polymorphic:Owner;"`
}

type Toy struct {
  ID        int
  Name      string
  OwnerID   int
  OwnerType string
}

db.Create(&Dog{Name: "dog1", Toys: []Toy{{Name: "toy1"}, {Name: "toy2"}}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","dogs"), ("toy2","1","dogs")

You can change the polymorphic type value with tag polymorphicValue, for example:

type Dog struct {
  ID   int
  Name string
  Toys []Toy `gorm:"polymorphic:Owner;polymorphicValue:master"`
}

type Toy struct {
  ID        int
  Name      string
  OwnerID   int
  OwnerType string
}

db.Create(&Dog{Name: "dog1", Toy: []Toy{{Name: "toy1"}, {Name: "toy2"}}})
// INSERT INTO `dogs` (`name`) VALUES ("dog1")
// INSERT INTO `toys` (`name`,`owner_id`,`owner_type`) VALUES ("toy1","1","master"), ("toy2","1","master")

7.3.4 Self-Referential Has Many

type User struct {
  gorm.Model
  Name      string
  ManagerID *uint
  Team      []User `gorm:"foreignkey:ManagerID"`
}

7.3.5 FOREIGN KEY Constraints

You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:

type User struct {
  gorm.Model
  CreditCards []CreditCard `gorm:"constraint:OnUpdate:CASCADE,OnDelete:SET NULL;"`
}

type CreditCard struct {
  gorm.Model
  Number string
  UserID uint
}

You are also allowed to delete selected has many associations with Select when deleting, checkout Delete with Select for details

7.4 Many To Many

Many to many add a join table between two models

For example, if your application includes users and languages, and a user can speak many languages, and many users can speak a specified language

type User struct {
  gorm.Model
  Languages []Language `gorm:"many2many:user_languages;"`
}

type Language struct {
  gorm.Model
  Name string
}

When using GORM AutoMigrate to create a table for User, GORM will create the join table automatically

7.4.1 Back-Reference

type User struct {
  gorm.Model
  Languages []*Language `gorm:"many2many:user_languages;"`
}

type Language struct {
  gorm.Model
  Name string
  Users []*User `gorm:"many2many:user_languages;"`
}

7.4.2 Override Foreign Key

To override them, you can use tag foreignKey, references, joinForeignKey, joinReferences, not necessary to use them together, you can just use one of them to override some foreign keys/references

type User struct {
  gorm.Model
  Profiles []Profile `gorm:"many2many:user_profiles;foreignKey:Refer;joinForeignKey:UserReferID;References:UserRefer;joinReferences:ProfileRefer"`
  Refer    uint      `gorm:"index:,unique"`
}

type Profile struct {
  gorm.Model
  Name      string
  UserRefer uint `gorm:"index:,unique"`
}

// Which creates join table: user_profiles
//   foreign key: user_refer_id, reference: users.refer
//   foreign key: profile_refer, reference: profiles.user_refer

NOTE: Some databases only allow create database foreign keys that reference on a field having unique index, so you need to specify the unique index tag if you are creating database foreign keys when migrating

7.4.3 Self-Referential Many2Many

Self-referencing many2many relationship

type User struct {
  gorm.Model
  Friends []*User `gorm:"many2many:user_friends"`
}

// Which creates join table: user_friends
//   foreign key: user_id, reference: users.id
//   foreign key: friend_id, reference: users.id

7.4.4 Customize JoinTable

JoinTable can be a full-featured model, like having Soft Delete，Hooks supports and more fields, you can setup it with SetupJoinTable, for example:

NOTE: Customized join table’s foreign keys required to be composited primary keys or composited unique index

type Person struct {
  ID        int
  Name      string
  Addresses []Address `gorm:"many2many:person_addresses;"`
}

type Address struct {
  ID   uint
  Name string
}

type PersonAddress struct {
  PersonID  int `gorm:"primaryKey"`
  AddressID int `gorm:"primaryKey"`
  CreatedAt time.Time
  DeletedAt gorm.DeletedAt
}

func (PersonAddress) BeforeCreate(db *gorm.DB) error {
  // ...
}

// Change model Person's field Addresses' join table to PersonAddress
// PersonAddress must defined all required foreign keys or it will raise error
err := db.SetupJoinTable(&Person{}, "Addresses", &PersonAddress{})

7.4.5 FOREIGN KEY Constraints

You can setup OnUpdate, OnDelete constraints with tag constraint, it will be created when migrating with GORM, for example:

type User struct {
  gorm.Model
  Languages []Language `gorm:"many2many:user_speaks;"`
}

type Language struct {
  Code string `gorm:"primarykey"`
  Name string
}

// CREATE TABLE `user_speaks` (`user_id` integer,`language_code` text,PRIMARY KEY (`user_id`,`language_code`),CONSTRAINT `fk_user_speaks_user` FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL ON UPDATE CASCADE,CONSTRAINT `fk_user_speaks_language` FOREIGN KEY (`language_code`) REFERENCES `languages`(`code`) ON DELETE SET NULL ON UPDATE CASCADE);

You are also allowed to delete selected many2many relations with Select when deleting, checkout Delete with Select for details

7.4.6 Composite Foreign Keys

If you are using Composite Primary Keys for your models, GORM will enable composite foreign keys by default

You are allowed to override the default foreign keys, to specify multiple foreign keys, just separate those keys’ name by commas, for example:

type Tag struct {
  ID     uint   `gorm:"primaryKey"`
  Locale string `gorm:"primaryKey"`
  Value  string
}

type Blog struct {
  ID         uint   `gorm:"primaryKey"`
  Locale     string `gorm:"primaryKey"`
  Subject    string
  Body       string
  Tags       []Tag `gorm:"many2many:blog_tags;"`
  LocaleTags []Tag `gorm:"many2many:locale_blog_tags;ForeignKey:id,locale;References:id"`
  SharedTags []Tag `gorm:"many2many:shared_blog_tags;ForeignKey:id;References:id"`
}

// Join Table: blog_tags
//   foreign key: blog_id, reference: blogs.id
//   foreign key: blog_locale, reference: blogs.locale
//   foreign key: tag_id, reference: tags.id
//   foreign key: tag_locale, reference: tags.locale

// Join Table: locale_blog_tags
//   foreign key: blog_id, reference: blogs.id
//   foreign key: blog_locale, reference: blogs.locale
//   foreign key: tag_id, reference: tags.id

// Join Table: shared_blog_tags
//   foreign key: blog_id, reference: blogs.id
//   foreign key: tag_id, reference: tags.id

7.5 Association Mode

7.5.1 Auto Create/Update

GORM will auto-save associations and its reference using Upsert when creating/updating a record.

user := User{
  Name:            "jinzhu",
  BillingAddress:  Address{Address1: "Billing Address - Address 1"},
  ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
  Emails:          []Email{
    {Email: "[email protected]"},
    {Email: "[email protected]"},
  },
  Languages:       []Language{
    {Name: "ZH"},
    {Name: "EN"},
  },
}

db.Create(&user)
// BEGIN TRANSACTION;
// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"), ("Shipping Address - Address 1") ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
// INSERT INTO "emails" (user_id,email) VALUES (111, "[email protected]"), (111, "[email protected]") ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "languages" ("name") VALUES ('ZH'), ('EN') ON DUPLICATE KEY DO NOTHING;
// INSERT INTO "user_languages" ("user_id","language_id") VALUES (111, 1), (111, 2) ON DUPLICATE KEY DO NOTHING;
// COMMIT;

db.Save(&user)

If you want to update associations’s data, you should use the FullSaveAssociations mode:

db.Session(&gorm.Session{FullSaveAssociations: true}).Updates(&user)
// ...
// INSERT INTO "addresses" (address1) VALUES ("Billing Address - Address 1"), ("Shipping Address - Address 1") ON DUPLICATE KEY SET address1=VALUES(address1);
// INSERT INTO "users" (name,billing_address_id,shipping_address_id) VALUES ("jinzhu", 1, 2);
// INSERT INTO "emails" (user_id,email) VALUES (111, "[email protected]"), (111, "[email protected]") ON DUPLICATE KEY SET email=VALUES(email);
// ...

7.5.2 Skip Auto Create/Update

To skip the auto save when creating/updating, you can use Select or Omit, for example:

user := User{
  Name:            "jinzhu",
  BillingAddress:  Address{Address1: "Billing Address - Address 1"},
  ShippingAddress: Address{Address1: "Shipping Address - Address 1"},
  Emails:          []Email{
    {Email: "[email protected]"},
    {Email: "[email protected]"},
  },
  Languages:       []Language{
    {Name: "ZH"},
    {Name: "EN"},
  },
}

db.Select("Name").Create(&user)
// INSERT INTO "users" (name) VALUES ("jinzhu", 1, 2);

db.Omit("BillingAddress").Create(&user)
// Skip create BillingAddress when creating a user

db.Omit(clause.Associations).Create(&user)
// Skip all associations when creating a user

NOTE

For many2many associations, GORM will upsert the associations before creating the join table references, if you want to skip the upserting of associations, you could skip it like:

db.Omit("Languages.*").Create(&user)

The following code will skip the creation of the association and its references

db.Omit("Languages").Create(&user)

7.5.3 Select/Omit Association fields

user := User{
  Name:            "jinzhu",
  BillingAddress:  Address{Address1: "Billing Address - Address 1", Address2: "addr2"},
  ShippingAddress: Address{Address1: "Shipping Address - Address 1", Address2: "addr2"},
}

// Create user and his BillingAddress, ShippingAddress
// When creating the BillingAddress only use its address1, address2 fields and omit others
db.Select("BillingAddress.Address1", "BillingAddress.Address2").Create(&user)

db.Omit("BillingAddress.Address2", "BillingAddress.CreatedAt").Create(&user)

7.5.4 Association Mode

Assocication Mode contains some commonly used helper methods to handle relationships

// Start Association Mode
var user User
db.Model(&user).Association("Languages")
// `user` is the source model, it must contains primary key
// `Languages` is a relationship's field name
// If the above two requirements matched, the AssociationMode should be started successfully, or it should return error
db.Model(&user).Association("Languages").Error

Find Associations

Find matched associations

	// SELECT `languages`.`id`,`languages`.`name` FROM `languages`
	// JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id`
	// AND `user_languages`.`user_id` = 1
	// WHERE name IN ('lang_0')
	var languages []Language
	db.Model(&User{ID: 1}).Where("name IN ?", []string{"lang_0"}).Association("Languages").Find(&languages)
	utils.PrintRecord(languages)

Append Associations

Append new associations for many to many,has many

replace current association for has one,belongs to

many to many has many 新增 association

	// append new associations
	// SELECT `languages`.`id`,`languages`.`name` FROM `languages`
	// JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id`
	// AND `user_languages`.`user_id` = 1
	// WHERE name IN ('lang_0')
	var languages []Language
	db.Model(&User{ID: 1}).Where("name IN ?", []string{"lang_0"}).Association("Languages").Find(&languages)
	utils.PrintRecord(languages)

has one belongs to 替换 association

// append association
	// SELECT * FROM `credit_cards` WHERE `credit_cards`.`user_id` = 1
	// INSERT INTO `credit_cards` (`number`,`user_id`) VALUES ("new_xxx",2) ON CONFLICT (`id`) DO UPDATE SET `user_id`=`excluded`.`user_id` RETURNING `id`
	db.Model(&User{ID: 2}).Association("CreditCard").Append(&CreditCard{Number: "new_xxx"})
	// INSERT INTO `credit_cards` (`number`,`user_id`) VALUES ("new_2nd_xxx",2) ON CONFLICT (`id`) DO UPDATE SET 					`user_id`=`excluded`.`user_id` RETURNING `id`
	// UPDATE `credit_cards` SET `user_id`=NULL WHERE `credit_cards`.`id` <> 12 AND `credit_cards`.`user_id` = 2
	db.Model(&User{ID: 2}).Association("CreditCard").Append(&CreditCard{Number: "new_2nd_xxx"})

可以看到，在为 ID 为 2 的 user append 新的关联的时候，新增关联然后去除原有的关联，从而实现了替换原关联的作用

Tips:

SQLITE3 不支持 RIGHT JOIN 和 FULL JOIN, 但是可以通过 LEFT JOIN 来进行模拟

SELECT u.id AS userID,u.name,c.number,c.id AS creditCardID FROM users AS u LEFT JOIN credit_cards AS c ON u.id = c.user_id 
UNION ALL
SELECT u.id,u.name,c.number,c.id FROM credit_cards AS c LEFT JOIN users AS u ON c.user_id = u.id ;

Replace Associations

Replace current associations with new ones

// replace with new association
	// INSERT INTO `languages` (`name`) VALUES ("replaced_lang") ON CONFLICT DO NOTHING RETURNING `id`
	// INSERT INTO `user_languages` (`user_id`,`language_id`) VALUES (2,24) ON CONFLICT DO NOTHING
	// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2 AND `user_languages`.`language_id` <> 24
	db.Model(&User{ID: 2}).Association("Languages").Replace(&Language{Name: "replaced_lang"})

使用 FULL JOIN 查看数据

SELECT u.id AS userID,u.name,l.name AS lang,l.id AS langID FROM users AS u LEFT JOIN user_languages AS ul ON ul.user_id = u.id LEFT JOIN languages AS l ON l.id = ul.language_id
UNION ALL
SELECT u.id AS userID,u.name,l.name AS lang,l.id AS langID FROM languages AS l LEFT JOIN user_languages AS ul ON ul.language_id = l.id LEFT JOIN users AS u ON u.id = ul.user_id;

Delete Associations

Remove the relationship between source & arguments if exists, only delete the reference, won’t delete those objects from DB

	// delete association
	// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2 AND `user_languages`.`language_id` = 26
	db.Model(&User{ID: 2}).Association("Languages").Delete(&Language{ID: 26})

Clear Associations

Remove all reference between soruce & association, won’t delete those associations

	// clear associations
	// DELETE FROM `user_languages` WHERE `user_languages`.`user_id` = 2
	db.Model(&User{ID: 2}).Association("Languages").Clear()

Count Associations

Return the count of current associations

// SELECT count(*) FROM `languages` JOIN `user_languages` ON `user_languages`.`language_id` = `languages`.`id` AND `user_languages`.`user_id` = 1
db.Model(&User{ID: 1}).Association("Languages").Count()

Batch Data

// Find all roles for all users
db.Model(&users).Association("Role").Find(&roles)

// Delete User A from all user's team
db.Model(&users).Association("Team").Delete(&userA)

// Get distinct count of all users' teams
db.Model(&users).Association("Team").Count()

// For `Append`, `Replace` with batch data, the length of the arguments needs to be equal to the data's length or else it will return an error
var users = []User{user1, user2, user3}
// e.g: we have 3 users, Append userA to user1's team, append userB to user2's team, append userA, userB and userC to user3's team
db.Model(&users).Association("Team").Append(&userA, &userB, &[]User{userA, userB, userC})
// Reset user1's team to userA，reset user2's team to userB, reset user3's team to userA, userB and userC
db.Model(&users).Association("Team").Replace(&userA, &userB, &[]User{userA, userB, userC})

7.5.5 Delete with Select

Delete with Select

You are allowed to delete selected has one/has many/many2many relations with Select when deleting records, for example:

// delete user's account when deleting user
db.Select("Account").Delete(&user)

// delete user's Orders, CreditCards relations when deleting user
db.Select("Orders", "CreditCards").Delete(&user)

// delete user's has one/many/many2many relations when deleting user
db.Select(clause.Associations).Delete(&user)

// delete each user's account when deleting users
db.Select("Account").Delete(&users)

NOTE: Associations will only be deleted if the deleting records’s primary key is not zero, GORM will use those priamry keys as conditions to delete selected associations

// DOESN'T WORK
db.Select("Account").Where("name = ?", "jinzhu").Delete(&User{})
// will delete all user with name `jinzhu`, but those user's account won't be deleted

db.Select("Account").Where("name = ?", "jinzhu").Delete(&User{ID: 1})
// will delete the user with name = `jinzhu` and id = `1`, and user `1`'s account will be deleted

db.Select("Account").Delete(&User{ID: 1})
// will delete the user with id = `1`, and user `1`'s account will be deleted

7.5.6 Association Tags

Tag	Description
foreignKey	Specifies column name of the current model that is used as a foreign key to the join table
references	Specifies column name of the reference’s table that is mapped to the foreign key of the join table
polymorphic	Specifies polymorphic type such as model name
polymorphicValue	Specifies polymorphic value, default table name
many2many	Specifies join table name
joinForeignKey	Specifies foreign key column name of join table that maps to the current table
joinReferences	Specifies foreign key column name of join table that maps to the reference’s table
constraint	Relations constraint, e.g: OnUpdate,OnDelete

7.6 Preloading(Eager Loading)

7.6.1 Preload

GORM allows eager loading relations in other SQL with Preload

package main

import (
	"gorm-note/utils"
	"log"
	"math/rand"
	"strconv"

	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

const (
	DBName    = "preloading.db"
	UserCount = 10
)

type User struct {
	ID     uint `gorm:"primaryKey"`
	Name   string
	Orders []Order
}

type Order struct {
	ID     uint `gorm:"primaryKey"`
	UserID uint
	Price  float64
}

func main() {
	db := initializeDB()
	sqlDB, _ := db.DB()
	defer sqlDB.Close()

	createTables(db)
	createUsers(db, UserCount)

	// Preloading
	// SELECT * FROM `orders` WHERE `orders`.`user_id` = 1
	// SELECT * FROM `users` WHERE id = 1
	var user User
	db.Preload("Orders").Where("id = ?", 1).Find(&user)
	utils.PrintRecord(user)
}

func initializeDB() *gorm.DB {
	db, err := gorm.Open(sqlite.Open(DBName), &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info),
	})
	if err != nil {
		log.Fatalln("connect db failed: ", err.Error())
	}
	return db
}

func createTables(db *gorm.DB) {
	db.AutoMigrate(&User{})
	db.AutoMigrate(&Order{})
}

func createUsers(db *gorm.DB, num int) {
	var count int64
	db.Model(&User{}).Count(&count)
	if count > 0 {
		return
	}
	users := make([]User, num)
	for i := 0; i < num; i++ {
		name := "user_" + strconv.Itoa(i)
		price := rand.Float64() * float64(i)
		price2 := rand.Float64() * float64(i)
		users[i] = User{Name: name, Orders: []Order{{Price: price}, {Price: price2}}}
	}
	db.Create(&users)
}

7.6.2 Joins Preloading

Preload loads the association data in a separate query, Join Prelod will loads association data using inner join

NOTE

Join Preload works with one-to-one relation, e.g: has one, belongs to

// Joins preload work with has-one or belongs-to
	// SELECT `users`.`id`,`users`.`name`,`CreditCard`.`id` AS `CreditCard__id`,`CreditCard`.`user_id` AS `CreditCard__user_id`,`CreditCard`.`number` AS `CreditCard__number` FROM `users`
	// LEFT JOIN `credit_cards` `CreditCard` ON `users`.`id` = `CreditCard`.`user_id` WHERE `users`.`id` = 2 LIMIT 1
	var user3 User
	db.Joins("CreditCard").Take(&user3, 2)
	utils.PrintRecord(user3)
	// not working, will cause panic
	var user2 User
	db.Joins("Orders").Find(&user2, 2)
	utils.PrintRecord(user2)

当 joins preloading 应用在 has-many, many-to-many 的情形下时将会触发 panic

7.6.3 Preload All

clause.Association can work with Preload similar like Select when creating/updating, you can use it to Preload all associations

// SELECT * FROM `credit_cards` WHERE `credit_cards`.`user_id` IN (1,2,3,4,5,6,7,8,9,10)
	// SELECT * FROM `orders` WHERE `orders`.`user_id` IN (1,2,3,4,5,6,7,8,9,10)
	// SELECT * FROM `users`
	var users []User
	db.Preload(clause.Associations).Find(&users)
	utils.PrintRecord(users)

cluase.Association won’t preload nested associations, but you can use it with Nested Preloading together

db.Preload("Orders.OrderItems.Product").Preload(clause.Associations).Find(&users)

7.6.4 Preload with conditions

GORM allows Preload associations with conditions, it works similar to inline condition

Preload 的内联条件是针对关联表的

	// preload with conditions
	// SELECT * FROM `orders` WHERE `orders`.`user_id` = 2 AND price > 0.500000
	// SELECT * FROM `users` WHERE id = 2
	var user4 User
	db.Where("id = ?", 2).Preload("Orders", "price > ?", 0.5).Find(&user4)
	utils.PrintRecord(user4)

7.6.5 Custom Preloading SQL

You are able to custom preloading SQL by passing in func(db *gorm.DB) *gorm.DB

// custom preloading SQL
	// SELECT * FROM `orders` WHERE `orders`.`user_id` = 2 ORDER BY price
	// SELECT * FROM `users` WHERE id = 2
	var user5 User
	db.Where(&User{ID: 2}).Preload("Orders", func(db *gorm.DB) *gorm.DB {
		return db.Order("price")
	}).Find(&user5)
	utils.PrintRecord(user5)

7.6.6 Nested Preloading

GORM supports nested preloading, for example:

db.Preload("Orders.OrderItems.Product").Preload("CreditCard").Find(&users)

// Customize Preload conditions for `Orders`
// And GORM won't preload unmatched order's OrderItems then
db.Preload("Orders", "state = ?", "paid").Preload("Orders.OrderItems").Find(&users)

Reference

1. gorm
2. What is the difference between Find and Scan， Can I replace Scan with Find ？ grom issue
3. What is the syntax for SELECT IN statement for SQLITE? stackoverflow
4. SQLite FULL OUTER JOIN Emulation sqlitetutorial