gorm Preload与Joins带条件查询--闭坑指南与示例

• 一. Preload与Joins的区别
• 二. 使用示例
• • 1. Preload
  • • 1.1 Preload 带条件参数查询
    • • 例子1：有个活动需要带所有孩子都参加，但是每个孩子都只能带名为**纸飞机**的玩具。
      • 例子2：但是每个人只展示一个玩具
    • 2 Joins
    • • 2.1 Joins 带条件查询
      • 2.2 Joins 模糊查询
      • 2.3 使Joins支持一对多关系的查询
      • 2.4 Joins方法的错误示范

      一. Preload与Joins的区别

      • Preload 是gorm特有的方法，用于自动加载关联字段（ belongTo 、 many2many 、 hasOne 、 hasMany ）中的数据。
      • Joins 属于SQL语法中的一部分，使用 First 、 Find 等方法时，会生成对应的SQL语句。一个 Jions 方法只支持一对一( hasOne , belongTo )关系和一对多关系（ hasMany ），多对多关系需要两个 Joins 方法（因为要多关联一个中间表）。

      二. 使用示例

      示例中用到的结构体如下

      type Child struct {
      	gorm.Model
      	ChildName string `gorm:"column:child_name;unique"`
      	Toys      []Toy  `gorm:"foreignKey:ChildId"` //关联字段 hasMany
      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"`  //关联字段 belongTo
      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": "玩具兵"