一对多关系的介绍
这里女神表里面有多个舔狗，就是一对多has many关系
并且舔狗表里面包含Info信息表，是has one关系
type GirlGOD struct { gorm.Model Name string Dog []Dog } type Dog struct { gorm.Model Name string GirlGodID uint Info Info } type Info struct { gorm.Model Age int Money int DogID uint } func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) info1 := Info{ Age: 1, Money: 1, } info2 := Info{ Age: 2, Money: 2, } dog1 := Dog{ Name: "dog1", Info: info1, } dog2 := Dog{ Name: "dog2", Info: info2, } girl1 := GirlGOD{ Name: "girl1", Dogs: []Dog{dog1}, } girl2 := GirlGOD{ Name: "girl2", Dogs: []Dog{dog2}, } db.Debug().Create(&girl1) db.Debug().Create(&girl2) }
预加载
Preload预加载
Preload 在一个单独查询中加载关联数据。
不使用预加载
查不出关联的舔狗的信息
func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) var girls []GirlGOD db.Debug().Find(&girls) fmt.Println(girls) for _, v := range girls { for i := range v.Dogs { fmt.Println(v.Name, v.Dogs[i].Name, v.Dogs[i].Info.Money) } } }
2022/05/15 22:53:29 C:/Users/68725/Desktop/leetcode/main.go:32 [42.024ms] [rows:2] SELECT * FROM `girl_gods` WHERE `girl_gods`.`deleted_at` IS NULL [{{1 2022-05-15 22:43:14.714 +0800 CST 2022-05-15 22:43:14.714 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl1 []} {{2 2022-05-15 22:43:14.756 +0800 CST 2022-05-15 22:43:14.756 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl2 []}]
使用预加载
查出与girlgod关联的dog表的信息，但是与dog表关联的info表信息没有
func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) var girls []GirlGOD db.Debug().Preload("Dogs").Find(&girls) fmt.Println(girls) for _, v := range girls { for i := range v.Dogs { fmt.Println(v.Name, v.Dogs[i].Name, v.Dogs[i].Info.Money) } } }
2022/05/15 22:54:22 C:/Users/68725/Desktop/leetcode/main.go:32 [0.651ms] [rows:2] SELECT * FROM `dogs` WHERE `dogs`.`girl_god_id` IN (1,2) AND `dogs`.`deleted_at` IS NULL 2022/05/15 22:54:22 C:/Users/68725/Desktop/leetcode/main.go:32 [49.459ms] [rows:2] SELECT * FROM `girl_gods` WHERE `girl_gods`.`deleted_at` IS NULL [{{1 2022-05-15 22:43:14.714 +0800 CST 2022-05-15 22:43:14.714 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl1 [{{1 2022-05-15 22:43:14.752 +0800 CST 2022-05-15 22:43:14.752 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} dog1 1 {{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} 0 0 0}}]} {{2 2022-05-15 22:43:14.756 +0800 CST 2022-05-15 22:43:14.756 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl2 [{{2 2022-05-15 22:43:14.757 +0800 CST 2022-05-15 22:43:14.757 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} dog2 2 {{0 0001-01-01 00:00:00 +0000 UTC 0001-01-01 00:00:00 +0000 UTC {0001-01-01 00:00:00 +0000 UTC false}} 0 0 0}}]}] girl1 dog1 0 girl2 dog2 0
func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) var girls []GirlGOD db.Debug().Preload("Dogs.Info").Preload("Dogs").Find(&girls) fmt.Println(girls) for _, v := range girls { for i := range v.Dogs { fmt.Println(v.Name, v.Dogs[i].Name, v.Dogs[i].Info.Money) } } }
使用链式预加载
将与girlgod关联的dog表的信息，dog表与info表关联的信息都查出来
2022/05/15 22:55:26 C:/Users/68725/Desktop/leetcode/main.go:32 [0.000ms] [rows:2] SELECT * FROM `infos` WHERE `infos`.`dog_id` IN (1,2) AND `infos`.`deleted_at` IS NULL 2022/05/15 22:55:26 C:/Users/68725/Desktop/leetcode/main.go:32 [1.531ms] [rows:2] SELECT * FROM `dogs` WHERE `dogs`.`girl_god_id` IN (1,2) AND `dogs`.`deleted_at` IS NULL 2022/05/15 22:55:26 C:/Users/68725/Desktop/leetcode/main.go:32 [44.470ms] [rows:2] SELECT * FROM `girl_gods` WHERE `girl_gods`.`deleted_at` IS NULL [{{1 2022-05-15 22:43:14.714 +0800 CST 2022-05-15 22:43:14.714 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl1 [{{1 2022-05-15 22:43:14.752 +0800 CST 2022-05-15 22:43:14.752 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} dog1 1 {{1 2022-05-15 22:43:14.753 +0800 CST 2022-05-15 22:43:14.753 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 1 1 1}}]} {{2 2022-05-15 22:43:14.756 +0800 CST 2022-05-15 22:43:14.756 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl2 [{{2 2022-05-15 22:43:14.757 +0800 CST 2022-05-15 22:43:14.757 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} dog2 2 {{2 2022-05-15 22:43:14.757 +0800 CST 2022-05-15 22:43:14.757 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 2 2 2}}]}] girl1 dog1 1 girl2 dog2 2
条件预加载
func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) var girls []GirlGOD db.Debug().Preload("Dogs.Info", "money>0").Preload("Dogs", "name=?", "dog2").Find(&girls) fmt.Println(girls) for _, v := range girls { for i := range v.Dogs { fmt.Println(v.Name, v.Dogs[i].Name, v.Dogs[i].Info.Money) } } }
2022/05/15 22:56:47 C:/Users/68725/Desktop/leetcode/main.go:32 [1.509ms] [rows:1] SELECT * FROM `infos` WHERE `infos`.`dog_id` = 2 AND money>0 AND `infos`.`deleted_at` IS NULL 2022/05/15 22:56:47 C:/Users/68725/Desktop/leetcode/main.go:32 [1.509ms] [rows:1] SELECT * FROM `dogs` WHERE `dogs`.`girl_god_id` IN (1,2) AND name='dog2' AND `dogs`.`deleted_at` IS NULL 2022/05/15 22:56:47 C:/Users/68725/Desktop/leetcode/main.go:32 [42.171ms] [rows:2] SELECT * FROM `girl_gods` WHERE `girl_gods`.`deleted_at` IS NULL [{{1 2022-05-15 22:43:14.714 +0800 CST 2022-05-15 22:43:14.714 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl1 []} {{2 2022-05-15 22:43:14.756 +0800 CST 2022-05-15 22:43:14.756 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} girl2 [{{2 2022-05-15 22:43:14.757 +0800 CST 2022-05-15 22:43:14.757 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} dog2 2 {{2 2022-05-15 22:43:14.757 +0800 CST 2022-05-15 22:43:14.757 +0800 CST {0001-01-01 00:00:00 +0000 UTC false}} 2 2 2}}]}] girl2 dog2 2
Joins 预加载
Join Preload 会使用 inner join 加载关联数据
注意 Join Preload 适用于一对一的关系，例如： has one, belongs to
func main() { db, _ := gorm.Open(mysql.New(mysql.Config{DSN: "root:123456@tcp(127.0.0.1:3306)/gormDB?charset=utf8mb4&parseTime=True&loc=Local"}), &gorm.Config{}) db.AutoMigrate(&GirlGOD{}, &Dog{}, &Info{}) var girls []GirlGOD db.Debug().Preload("Dogs", func(db *gorm.DB) *gorm.DB { return db.Joins("Info").Where("money>1") }).Find(&girls) fmt.Println(girls) }
SELECT `dogs`.`id`, `dogs`.`created_at`, `dogs`.`updated_at`, `dogs`.`deleted_at`, `dogs`.`name`, `dogs`.`girl_god_id`, `Info`.`id` AS `Info__id`, `Info`.`created_at` AS `Info__created_at`, `Info`.`updated_at` AS `Info__updated_at`, `Info`.`deleted_at` AS `Info__deleted_at`, `Info`.`age` AS `Info__age`, `Info`.`money` AS `Info__money`, `Info`.`dog_id` AS `Info__dog_id` FROM `dogs` LEFT JOIN `infos` `Info` ON `dogs`.`id` = `Info`.`dog_id` AND `Info`.`deleted_at` IS NULL WHERE money > 1 AND `dogs`.`girl_god_id` IN (1, 2) AND `dogs`.`deleted_at` IS NULL
