14.GORM 关联查询

# 01.一对一

# 1.1 创建一对一model

models/article.go

package models
import (
_ "github.com/jinzhu/gorm"
)

type Article struct {
	Id          int         `json:"id"`
	Title       string      `json:"title"`
	CateId      string      `json:"cate_id"`
	State       int         `json:"state"`
	ArticleCate ArticleCate `gorm:"foreignkey:Id;association_foreignkey:CateId"`
   // Id指的是 ArticleCate 表的字段名Id
   // CateId 指的是自己表中的 字段名，关联ArticleCate表的Id
}

func (Article) TableName() string {
	return "article"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

models/articleCate.go

package models
import (
	_ "github.com/jinzhu/gorm"
)

type ArticleCate struct {
	Id      int       `json:"id"`
	Title   string    `json:"title"`
	State   int       `json:"state"`
}

func (ArticleCate) TableName() string {
	return "article_cate"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 1.2 一对一表查询

package controllers
import (
	"beegogorm/models"
	"github.com/astaxie/beego"
)

type ArticleController struct {
	beego.Controller
}

func (c *ArticleController) ArticleOrm() {
	// 1、查询文章信息的时候关联文章分类  (1对1)
	article := []models.Article{}
	models.DB.Preload("ArticleCate").Find(&article)

	// 2、查询文章信息的时候关联文章分类  (1对1) 添加过来条件
	article := []models.Article{}
	models.DB.Preload("ArticleCate").Where("id>2").Find(&article)

	c.Data["json"] = article
	c.ServeJSON()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22

# 1.3 查询结果

Article(文章表) 和 ArticleCate文章分类表
一篇文章只能有一个分类

[
  {
    "id": 1,
    "title": "西游记",
    "cate_id": "1",
    "state": 1,
    "ArticleCate": {
      "id": 1,
      "title": "四大名著",
      "state": 1
    }
  },
  {
    "id": 2,
    "title": "三国演义",
    "cate_id": "1",
    "state": 1,
    "ArticleCate": {
      "id": 1,
      "title": "四大名著",
      "state": 1
    }
  },
  {
    "id": 3,
    "title": "货币战争",
    "cate_id": "2",
    "state": 1,
    "ArticleCate": {
      "id": 2,
      "title": "国外名著",
      "state": 1
    }
  },
  {
    "id": 4,
    "title": "钢铁是怎样炼成的",
    "cate_id": "2",
    "state": 1,
    "ArticleCate": {
      "id": 2,
      "title": "国外名著",
      "state": 1
    }
  }
]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46

# 1.4 可以使用自动创建表

models/main.go

package models

import (
	"github.com/astaxie/beego"
	"github.com/jinzhu/gorm"
	_ "github.com/jinzhu/gorm/dialects/mysql"
)

var DB *gorm.DB
var err error

func init() {
	//和数据库建立连接
	DB, err = gorm.Open("mysql", "root:chnsys@2016@/beegodb?charset=utf8&parseTime=True&loc=Local")
	if err != nil {
		beego.Error()
	}

	//// 创建表
	//DB.CreateTable(&Article{},&ArticleCate{})     // 根据User结构体建表
	//// 设置表结构的存储引擎为InnoDB
	//DB.Set("gorm:table_options", "ENGINE=InnoDB").CreateTable(&Article{},ArticleCate{})
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

# 02.一对多

# 2.1 创建一对多model

models/article.go

package models
import (
_ "github.com/jinzhu/gorm"
)

type Article struct {
	Id          int         `json:"id"`
	Title       string      `json:"title"`
	CateId      string      `json:"cate_id"`
	State       int         `json:"state"`
}

func (Article) TableName() string {
	return "article"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

models/articleCate.go

package models
import (
	_ "github.com/jinzhu/gorm"
)

type ArticleCate struct {
	Id      int       `json:"id"`
	Title   string    `json:"title"`
	State   int       `json:"state"`
}

func (ArticleCate) TableName() string {
	return "article_cate"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

# 2.2 一对多表查询

func (c *ArticleController) ArticleOrm() {
	// 1、查询文章分类信息的时候关联文章  (1对多)   查询文章分类显示文章信息
	articleCate := []models.ArticleCate{}
	models.DB.Preload("Article").Find(&articleCate)

	// 4、查询文章分类信息的时候关联文章   条件判断
	// articleCate := []models.ArticleCate{}
	// models.DB.Preload("Article").Where("id>1").Find(&articleCate)

	c.Data["json"] = articleCate
	c.ServeJSON()
}

1
2
3
4
5
6
7
8
9
10
11
12

# 2.3 查询结果

Article(文章表) 和 ArticleCate文章分类表
一个文章分类下面，有多篇文章

[
  {
    "id": 1,
    "title": "四大名著",
    "state": 1,
    "Article": [
      {
        "id": 1,
        "title": "西游记",
        "cate_id": "1",
        "state": 1,
        "ArticleCate": {
          "id": 0,
          "title": "",
          "state": 0,
          "Article": null
        }
      },
      {
        "id": 2,
        "title": "三国演义",
        "cate_id": "1",
        "state": 1,
        "ArticleCate": {
          "id": 0,
          "title": "",
          "state": 0,
          "Article": null
        }
      }
    ]
  },
  {
    "id": 2,
    "title": "国外名著",
    "state": 1,
    "Article": [
      {
        "id": 3,
        "title": "货币战争",
        "cate_id": "2",
        "state": 1,
        "ArticleCate": {
          "id": 0,
          "title": "",
          "state": 0,
          "Article": null
        }
      },
      {
        "id": 4,
        "title": "钢铁是怎样炼成的",
        "cate_id": "2",
        "state": 1,
        "ArticleCate": {
          "id": 0,
          "title": "",
          "state": 0,
          "Article": null
        }
      }
    ]
  }
]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64

# 03.多对多

# 2.1 创建多对多model

models/lesson.go

package models
import (
	_ "github.com/jinzhu/gorm"
)

type Lesson struct {
	Id      int       `json:"id"`
	Name    string    `json:"name"`
	Student []Student `gorm:"many2many:lesson_student;"`
	// lesson_student => 表名（第三张关联表表名）
}

func (Lesson) TableName() string {
	return "lesson"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

models/student.go

package models
import (
	_ "github.com/jinzhu/gorm"
)

type ArticleCate struct {
	Id      int       `json:"id"`
	Title   string    `json:"title"`
	State   int       `json:"state"`
}

func (ArticleCate) TableName() string {
	return "article_cate"
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14

models/lessonStudent.go

package models
import (
	_ "github.com/jinzhu/gorm"
)

type LessonStudent struct {
	LessonId  int `json:"lesson_id"`
	StudentId int `json:"student_id"`
}

func (LessonStudent) TableName() string {
	return "lesson_student"
}

1
2
3
4
5
6
7
8
9
10
11
12
13

# 2.2 多对多表查询

func (c *StudentController) StudentM2M() {
	////1、获取学生信息
	studentList := []models.Student{}
	models.DB.Find(&studentList)
	c.Data["json"] = studentList
	c.ServeJSON()

	////2、获取课程信息
	lessonList := []models.Lesson{}
	models.DB.Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	//3、查询学生信息的时候获取学生的选课信息
	studentList := []models.Student{}
	models.DB.Preload("Lesson").Find(&studentList)
	c.Data["json"] = studentList
	c.ServeJSON()

	//4、查询张三选修了哪些课程
	studentList := []models.Student{}
	models.DB.Preload("Lesson").Where("id=1").Find(&studentList)
	c.Data["json"] = studentList
	c.ServeJSON()

	//5、课程被哪些学生选修了
	lessonList := []models.Lesson{}
	models.DB.Preload("Student").Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	//6、计算机网络被那些学生选修了
	lessonList := []models.Lesson{}
	models.DB.Preload("Student").Where("id=1").Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	//7、条件
	lessonList := []models.Lesson{}
	models.DB.Preload("Student").Offset(1).Limit(2).Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	//8、张三被开除了  查询课程被哪些学生选修的时候要去掉张三
	lessonList := []models.Lesson{}
	models.DB.Preload("Student", "id!=1").Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	lessonList := []models.Lesson{}
	models.DB.Preload("Student", "id not in (1,2)").Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	//9、查看课程被哪些学生选修  要求：学生id倒叙输出   自定义预加载 SQL
	//https://gorm.io/zh_CN/docs/preload.html
	lessonList := []models.Lesson{}
	models.DB.Preload("Student", func(db *gorm.DB) *gorm.DB {
		return models.DB.Order("id DESC")
	}).Find(&lessonList)
	c.Data["json"] = lessonList
	c.ServeJSON()

	lessonList := []models.Lesson{}
	models.DB.Preload("Student", func(db *gorm.DB) *gorm.DB {
		return models.DB.Where("id>3").Order("id DESC")
	}).Find(&lessonList)
	
	c.Data["json"] = lessonList
	c.ServeJSON()
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71

# 2.3 查询结果

学生表(Student) 和课程表(Lesson)
一个学生可以选修多个课程，一个课程也可以被多个学生选修

[
  {
    "Id": 1,
    "Number": "12",
    "Password": "123456",
    "ClassId": 1,
    "Name": "zhangsan",
    "Lesson": [
      {
        "id": 1,
        "name": "语文",
        "Student": null
      }
    ]
  },
  {
    "Id": 2,
    "Number": "24",
    "Password": "123456",
    "ClassId": 1,
    "Name": "lisi",
    "Lesson": [
      {
        "id": 1,
        "name": "语文",
        "Student": null
      }
    ]
  },
  {
    "Id": 3,
    "Number": "22",
    "Password": "123456",
    "ClassId": 1,
    "Name": "wangwu",
    "Lesson": [
      {
        "id": 2,
        "name": "数学",
        "Student": null
      }
    ]
  }
]

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

上次更新: 2024/3/13 15:35:10

← 13.GORM查询 15.GORM原生SQL→