gorm Preload与Joins带条件查询--闭坑指南与示例
一. Preload与Joins的区别
- Preload方法是用来加载关联字段（
belongTo、many2many、hasOne、hasMany）的数据的。 - gorm中的Joins方法仅适用的查询，无法加载关联字段内容。并且，gorm原生的方法只支持一对一关系(
has one,belongs to)。
二. 使用示例
示例中用到的结构体如下
type Child struct {
gorm.Model
ChildName string `gorm:"column:child_name;unique"`
Toys []Toy `gorm:"foreignKey:ChildId"`
}
func (Child) TableName() string {
return "child"
}
type Toy struct {
gorm.Model
Name string `gorm:"column:name"`
ChildId uint `gorm:"column:child_id"`
Child *Child `gorm:"foreignKey:ChildId"`
}
func (Toy) TableName() string {
return "toy"
}
并生成以下数据
var children = []Child{
{
ChildName: "刘涛",
Toys: []Toy{
{Name: "纸飞机"},
{Name: "小火车"},
},
},
{
ChildName: "王斌",
Toys: []Toy{
{Name: "玩具兵"},
},
},
}
db.Create(&children)
1. Preload
1.1 Preload 带条件参数查询
例子1：有个活动需要带所有孩子都参加，但是每个孩子都只能带名为纸飞机的玩具。
使用如下查询方法：
var children []Child
db.Preload("Toys", db.Where(&Toy{Name: "纸飞机"})).Find(&children)
//也可以写成
//db.Preload("Toys", "name = ?", "纸飞机").Find(&children)
等同于sql语句
SELECT * FROM "toy" WHERE "toy"."child_id" IN (1,2) AND name = '纸飞机' AND "toy"."deleted_at" IS NULL;
SELECT * FROM "child" WHERE "child"."deleted_at" IS NULL;
输出结果转为json(为了易于查看，省略了一些不必要的字段):
[
{
"ID": 1,
"ChildName": "刘涛",
"Toys": [
{
"ID": 1,
"ChildId": 1,
"Name": "纸飞机"
}
]
},
{
"ID": 2,
"ChildName": "王斌",
"Toys": []
}
]
可以看到，王斌小朋友也出现在了查询结果中，但是他的"Toys”是空的。因为你的查询条件只允许小朋友们带纸飞机。
例子2：但是每个人只展示一个玩具
实际开发中，通常用于预览页。示例代码如下：
var children []Child
limit1:=func(db *gorm.DB) *gorm.DB {return db.Limit(1)}
db.Preload("Toys",limit1).Find(&children)
2 Joins
2.1 Joins 带条件查询
因为joins只支持has one和 belongs to，所有这里的例子换个方向：查询哪些玩具是属于刘涛的。
查询示例如下:
var toys []Toy
//注意where中的表别名一定要加双引号
db.Joins("Child").Where("\"Child\".child_name = ?", "刘涛").Find(&toys)
//也可以写成这样，这里是不需要双引号的
db.Debug().Joins("Child").Clauses(clause.Eq{
Column: "Child.child_name",
Value: "刘涛",
}).Find(&toys)
等同于sql:
SELECT "toy"."id","toy"."created_at","toy"."updated_at","toy"."deleted_at","toy"."child_id","toy"."name","Child"."id" AS "Child__id","Child"."created_at" AS "Child__created_at","Child"."updated_at" AS "Child__updated_at","Child"."deleted_at" AS "Child__deleted_at","Child"."child_name" AS "Child__child_name" FROM "toy" LEFT JOIN "child" "Child" ON "toy"."child_id" = "Child"."id" AND "Child"."deleted_at" IS NULL WHERE "Child".child_name = '刘涛' AND "toy"."deleted_at" IS NULL
主要是这断话：LEFT JOIN "child" "Child"，gorm自动給表起了一个别名Child。
查询结果:
[
{
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.008533+08:00",
"UpdatedAt": "2022-11-09T19:51:38.008533+08:00",
"DeletedAt": null,
"ChildId": 1,
"Child": {
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.006354+08:00",
"UpdatedAt": "2022-11-09T19:51:38.006354+08:00",
"DeletedAt": null,
"ChildName": "刘涛",
"Toys": null
},
"Name": "纸飞机"
},
{
"ID": 2,
"CreatedAt": "2022-11-09T19:51:38.008533+08:00",
"UpdatedAt": "2022-11-09T19:51:38.008533+08:00",
"DeletedAt": null,
"ChildId": 1,
"Child": {
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.006354+08:00",
"UpdatedAt": "2022-11-09T19:51:38.006354+08:00",
"DeletedAt": null,
"ChildName": "刘涛",
"Toys": null
},
"Name": "小火车"
}
]
2.2 Joins 模糊查询
模糊查询最省力的方法是使用子句构造器,其它类似的方法就不展示了:
//查询拥有‘汽车’玩具的孩子们
db = db.Joins("Toys").Clauses(clause.Like{
Column: "Toys.name",
Value: "%" + "汽车" + "%",
}).Find(&children)
2.3 使Joins支持一对多关系的查询
这个方法其实也支持多对多关系，和sql语法是原理是一样的。
例子：查询有纸飞机玩具的孩子们，没有的孩子不展示。
var children []Child
db.Table(fmt.Sprintf("%v AS t1", Child{}.TableName())).
Joins(fmt.Sprintf("LEFT JOIN %v AS t2 on t1.id =t2.child_id ", Toy{}.TableName())).
Where("t2.name = ?", "纸飞机").Find(&children)
等同于sql语句
SELECT "t1"."id","t1"."created_at","t1"."updated_at","t1"."deleted_at","t1"."child_name" FROM child AS t1 left join toy t2 on t1.id =t2.child_id WHERE t2.name = '纸飞机' AND "t1"."deleted_at" IS NULL
查询结果
[
{
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.006354+08:00",
"UpdatedAt": "2022-11-09T19:51:38.006354+08:00",
"DeletedAt": null,
"ChildName": "刘涛",
"Toys": null
}
]
可以看到，查询结果没问题，但是Toys是空的。加一个Preload方法就行了
db.Preload("Toys").
Table(fmt.Sprintf("%v AS t1", Child{}.TableName())).
Joins(fmt.Sprintf("LEFT JOIN %v AS t2 on t1.id =t2.child_id ", Toy{}.TableName())).
Where("t2.name = ?", "纸飞机").Find(&children)
输出结果
[
{
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.006354+08:00",
"UpdatedAt": "2022-11-09T19:51:38.006354+08:00",
"DeletedAt": null,
"ChildName": "刘涛",
"Toys": [
{
"ID": 1,
"CreatedAt": "2022-11-09T19:51:38.008533+08:00",
"UpdatedAt": "2022-11-09T19:51:38.008533+08:00",
"DeletedAt": null,
"ChildId": 1,
"Child": null,
"Name": "纸飞机"
},
{
"ID": 2,
"CreatedAt": "2022-11-09T19:51:38.008533+08:00",
"UpdatedAt": "2022-11-09T19:51:38.008533+08:00",
"DeletedAt": null,
"ChildId": 1,
"Child": null,
"Name": "小火车"
}
]
}
]
2.4 Joins方法的错误示范
需要注意的是，Joins方法的查询条件需要放在Where方法中。
但是很多人会和Preload方法混淆，写成这样
//错误示例1
db.Joins("Child", db.Where(&Child{ChildName: "刘涛"})).Find(&toys)
//错误示例2
db.Joins("Child", "Child.child_name = '刘涛'").Find(&toys)
-
错误示例1 查询出来的玩具兵也变成刘涛的了，完全不对；
官方文档 中虽然有介绍到这个方法，但是实际使用中意义不明，而且还存在bug。
-
错误示例2 完全无意义的查询方法。
[ { "ID": 1, "CreatedAt": "2022-11-09T19:51:38.008533+08:00", "UpdatedAt": "2022-11-09T19:51:38.008533+08:00", "DeletedAt": null, "ChildId": 1, "Child": { "ID": 1, "CreatedAt": "2022-11-09T19:51:38.006354+08:00", "UpdatedAt": "2022-11-09T19:51:38.006354+08:00", "DeletedAt": null, "ChildName": "刘涛", "Toys": null }, "Name": "纸飞机" }, { "ID": 2, "CreatedAt": "2022-11-09T19:51:38.008533+08:00", "UpdatedAt": "2022-11-09T19:51:38.008533+08:00", "DeletedAt": null, "ChildId": 1, "Child": { "ID": 1, "CreatedAt": "2022-11-09T19:51:38.006354+08:00", "UpdatedAt": "2022-11-09T19:51:38.006354+08:00", "DeletedAt": null, "ChildName": "刘涛", "Toys": null }, "Name": "小火车" }, { "ID": 3, "CreatedAt": "2022-11-09T19:51:38.008533+08:00", "UpdatedAt": "2022-11-09T19:51:38.008533+08:00", "DeletedAt": null, "ChildId": 2, "Child": { "ID": 2, "CreatedAt": "2022-11-09T19:51:38.006354+08:00", "UpdatedAt": "2022-11-09T19:51:38.006354+08:00", "DeletedAt": null, "ChildName": "王斌", "Toys": null }, "Name": "玩具兵" } ]
1207
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
