解锁GORM框架的强大关联功能

176 阅读4分钟

解锁GORM框架的强大关联功能

一、一对一

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"
}
  • 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"
}

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()
}

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
    }
  }
]

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、创建一对多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"
}
  • 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"
}

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()
}

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、创建多对多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"
}
  • 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"
}
  • 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"
}

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()
}

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
      }
    ]
  }
]