预加载示例 preload + find/take
GORM允许使用 Preload
通过多个SQL中来直接加载关系, 例如:
type User struct {
gorm.Model
Username string
Orders []Order
}
type Order struct {
gorm.Model
UserID uint
Price float64
}
// 查找 user 时预加载相关 Order
db.Preload("Orders").Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4);
// 预加载Orders、Profile、Role多个关联属性 ps: 预加载字段,必须是User的属性
db.Preload("Orders").Preload("Profile").Preload("Role").Find(&users)
// SELECT * FROM users;
// SELECT * FROM orders WHERE user_id IN (1,2,3,4); // has many
// SELECT * FROM profiles WHERE user_id IN (1,2,3,4); // has one
// SELECT * FROM roles WHERE id IN (4,5,6); // belongs to
var u []User
db.Debug().Preload("Articles").Find(&u)
fmt.Println(u)
[1.486ms] [rows:7] SELECT * FROM `article` WHERE `article`.`user_id` IN (1,2,3)
[2.644ms] [rows:3] SELECT * FROM `user`
[{1 lucas [{1 golang 1} {2 k8s 1}]} {2 yanzi [{3 golang 2} {4 k8s 2}]} {3 test [{5 redis 3} {6 cherry 3} {7 marry 3}]}]
var u User
db.Debug().Where("name = ?", "lucas").Find(&u)
fmt.Printf("%#v\n", u)
db.Debug().Preload("Articles").Find(&u)
fmt.Printf("%#v\n", u)
var a Article
db.Debug().Take(&a, 8)
fmt.Printf("%#v\n", a)
db.Debug().Preload("User").Find(&a)
fmt.Printf("%#v\n", a)
[1.306ms] [rows:4] SELECT * FROM `article` WHERE `article`.`user_id` = 1
[3.628ms] [rows:1] SELECT * FROM `user` WHERE `user`.`id` = 1
main.User{ID:1, Name:"lucas", Articles:[]main.Article{main.Article{ID:1, Title:"k8s", UserID:1, User:main.User{ID:0, Name:"", Arti
cles:[]main.Article(nil)}}, main.Article{ID:2, Title:"golang", UserID:1, User:main.User{ID:0, Name:"", Articles:[]main.Article(nil
)}}, main.Article{ID:9, Title:"c", UserID:1, User:main.User{ID:0, Name:"", Articles:[]main.Article(nil)}}, main.Article{ID:10, Tit
le:"ruby", UserID:1, User:main.User{ID:0, Name:"", Articles:[]main.Article(nil)}}}}
[1.667ms] [rows:1] SELECT * FROM `article` WHERE `article`.`id` = 8 LIMIT 1
main.Article{ID:8, Title:"c++", UserID:3, User:main.User{ID:0, Name:"", Articles:[]main.Article(nil)}}
[1.124ms] [rows:1] SELECT * FROM `user` WHERE `user`.`id` = 3
[3.090ms] [rows:1] SELECT * FROM `article` WHERE `article`.`id` = 8
main.Article{ID:8, Title:"c++", UserID:3, User:main.User{ID:3, Name:"jerry", Articles:[]main.Article(nil)}}
preload的原理
其实很简单,你要preload user表的数据,gorm就提前把这张表全部取出来,然后再执行First
或Find
这样的方法去查询主数据,最后把两种数据通过外键关联一一对应起来。
其实preload也就是做了两次查询。
var u User
db.Debug().Preload("Articles").Take(&u, "name = ?", "test")
fmt.Println(u)
[2.007ms] [rows:3] SELECT * FROM `article` WHERE `article`.`user_id` = 3
[3.056ms] [rows:1] SELECT * FROM `user` WHERE name = 'test' LIMIT 1
{3 test [{5 redis 3} {6 cherry 3} {7 marry 3}]}
带条件预加载 只想展示某些
var u User
db.Debug().Where("name = ?", "lucas").Find(&u)
db.Debug().Preload("Articles", "id = ?", 2).Find(&u)
fmt.Println(u)
{1 lucas [{2 golang 1 {0 []}}]}
嵌套预加载
var a Article
db.Debug().Take(&a, 8)
db.Debug().Preload("User.Articles").Find(&a)
fmt.Printf("%#v\n", a)
[4.840ms] [rows:1] SELECT * FROM `article` WHERE `article`.`id` = 8
main.Article{ID:8, Title:"c++", UserID:3, User:main.User{ID:3, Name:"jerry", Articles:[]main.Article{main.Article{ID:8, Title:"c++", UserID:3, User:m
ain.User{ID:0, Name:"", Articles:[]main.Article(nil)}}}}}
级联删除
var u User
db.Debug().Where("name = ?", "lisa").Find(&u)
db.Debug().Select("Articles").Delete(&u)
[0.748ms] [rows:1] SELECT * FROM `user` WHERE name = 'lisa'
[2.106ms] [rows:1] DELETE FROM `article` WHERE `article`.`user_id` = 4
[6.887ms] [rows:1] DELETE FROM `user` WHERE `user`.`id` = 4
清除外键关系 Association + Delete、Clean
有了preload,那么user就可以通过.点出来。
db.Debug().Preload("Articles").Take(&u, 1
)
fmt.Println(u)
[4.167ms] [rows:1] SELECT * FROM `user` WHERE `user`.`id` = 1 LIMIT 1
{1 lucas [{1 k8s 1 {0 []}} {2 golang 1 {0 []}} {9 c 1 {0 []}} {10 ruby 1 {0 []}}]}
var u User
db.Take(&u, 2)
db.Model(&u).Association("Articles").Find(&u.Articles)
db.Debug().Model(&u).Association("Articles").Delete(&u.Articles)
[4.128ms] [rows:2] UPDATE `article` SET `user_id`=NULL WHERE `article`.`user_id` = 2 AND `article`.`id` IN (3,7)
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 2 | yanzi |
| 3 | lucas |
| 4 | yanzi |
+----+-------+
3 rows in set (0.00 sec)
mysql> select * from article;
+----+--------+---------+
| id | title | user_id |
+----+--------+---------+
| 1 | golang | NULL |
| 2 | k8s | NULL |
| 3 | c# | 2 |
| 4 | golang | 3 |
| 5 | k8s | 3 |
| 6 | c# | 4 |
| 7 | oracle | 2 |
+----+--------+---------+
7 rows in set (0.00 sec)
#修改之后
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 2 | yanzi |
| 3 | lucas |
| 4 | yanzi |
+----+-------+
mysql> select * from article;
+----+--------+---------+
| id | title | user_id |
+----+--------+---------+
| 1 | golang | NULL |
| 2 | k8s | NULL |
| 3 | c# | NULL |
| 4 | golang | 3 |
| 5 | k8s | 3 |
| 6 | c# | 4 |
| 7 | oracle | NULL |
+----+--------+---------+
清空关联关系示例:先清空关联关系,再删除
var u User
db.Take(&u, 1)
db.Debug().Model(&u).Association("Articles").Clear()
[11.177ms] [rows:2] UPDATE `article` SET `user_id`=NULL WHERE `article`.`user_id` = 1
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 1 | lucas |
| 2 | yanzi |
| 3 | lucas |
| 4 | yanzi |
+----+-------+
mysql> select * from article;
+----+--------+---------+
| id | title | user_id |
+----+--------+---------+
| 1 | golang | NULL |
| 2 | k8s | NULL |
| 3 | c# | 2 |
| 4 | golang | 3 |
| 5 | k8s | 3 |
| 6 | c# | 4 |
| 7 | oracle | 2 |
+----+--------+---------+
db.Debug().Delete(&u)
[9.960ms] [rows:1] DELETE FROM `user` WHERE `user`.`id` = 1
mysql> select * from user;
+----+-------+
| id | name |
+----+-------+
| 2 | yanzi |
| 3 | lucas |
| 4 | yanzi |
+----+-------+
mysql> select * from article;
+----+--------+---------+
| id | title | user_id |
+----+--------+---------+
| 1 | golang | NULL |
| 2 | k8s | NULL |
| 3 | c# | 2 |
| 4 | golang | 3 |
| 5 | k8s | 3 |
| 6 | c# | 4 |
| 7 | oracle | 2 |
+----+--------+---------+