初识Beego(二)-表关系处理

beego数据表结构，一对一，一对多，多对多

例子有点长，耐心看完
关系表结构：

type User struct {
	StuNo  int        `orm:"column(stu_no);pk;auto" json:"stuNo"`
	Name   string     `json:"name"`
	Age    int        `json:"age"`
	Course []*Courses `orm:"reverse(many)" json:"course"`
	Head   *Head      `orm:"rel(one)" json:"head"`
	Tags   []*Tags    `orm:"rel(m2m)"`
}
type Courses struct {
	ID     int    `orm:"column(id);pk;auto"`
	Name   string `json:"name"`
	CourID int    `json:"courID"`
	Score  int    `json:"score"`
	User   *User  `orm:"rel(fk)"`
}
type Head struct {
	ID     int `orm:"column(id);pk;auto"`
	Weight int
	Volume int
	User   *User `orm:"reverse(one)"`
}
type Tags struct {
	ID   int `orm:"column(id);pk;auto"`
	Name string
	User []*User `orm:"reverse(many)"`
}

一、一对一

User表和Head表是一对一关系
因为User表是rel(one), Head表是reverse(one),所以user表会自动生成head_id该字段，用来和head表联系起来

func (this *UserController) One2One() {
	// 新增
	head := new(models.Head)
	head.Volume = 12
	head.Weight = 24
	user := new(models.User)
	user.Name = "诺克萨斯之手"
	user.Age = 18
	head.User = user
	user.Head = head
	o := orm.NewOrm()
	o.Insert(head)
	o.Insert(user)

	// 查询
	// 1.单个查询
	user1 := &models.User{StuNo: 1}
	head1 := new(models.Head)
	// 这种查找，读出来数据，不能user1.Head.User。user1.Head里只存了对应的id
	if err := o.Read(user1); err != nil {
		fmt.Println("单个查询, err:", err)
	}
	if user1.Head != nil {
		o.Read(head1)
	}
	fmt.Println("user1:", user1)
	fmt.Println("user1.Head", user1.Head)
	fmt.Println("user1.Head.User", user1.Head.User)

	// 2.直接关联查询
	user2 := new(models.User)
	if err := o.QueryTable(user.TableName()).Filter("StuNo", 1).RelatedSel().One(user2); err != nil {
		fmt.Println("直接关联查询, err:", err)
	}
	fmt.Println("user2:", user2)
	fmt.Println("user2.Head", user2.Head)
	fmt.Println("user2.Head.User", user2.Head.User)

	// 3.通过User反向查询heads
	//head3 := new(models.Head)
	//if err := o.QueryTable("head").Filter("User__StuNo", 1).One(head3); err != nil {
	//	fmt.Println("通过User反向查询head, err:", err)
	//}
	//fmt.Println("head3", head3)

	// 删除-没有强依赖，可以选择随便删
	o.Delete(user)
	o.Delete(head)
}

二、多对一

Course表和User表是多对一关系
因为Course表是rel(fk),User表是reverse(many)，所以course表会自动生成user_id,用于关联user表

func (this *UserController) Many2One() {
	course1 := &models.Courses{
		Name: "语文",
		CourID: 1,
		Score: 99,
	}
	course2 := &models.Courses{
		Name: "数学",
		CourID: 2,
		Score: 100,
	}
	// 这里必须先处理user的一对一关系，要不然User表插入会报错
	head1 := &models.Head{
		Volume: 12,
		Weight: 24,
	}
	user1 := &models.User{
		Name: "压缩",
		Age: 18,
		Course: []*models.Courses{course1, course2},
		Head: head1,
	}
	orm.Debug=true
	o := orm.NewOrm()
	if n, err := o.Insert(head1); err != nil {
		fmt.Printf("Insert(head1), n=%d, err=%v\r\n", n, err)
	}
	// 先插入user，因为course表要存储和user的对应关系，要不然user_id会为0
	if n, err := o.Insert(user1); err != nil {
		fmt.Printf("Insert(user1), n=%d, err=%v\r\n", n, err)
	}
	// 这里必须给course把user关联上去
	course1.User = user1
	course2.User = user1
	if n, err := o.Insert(course1); err != nil {
		fmt.Printf("Insert(course1), n=%d, err=%v\r\n", n, err)
	}
	if n, err := o.Insert(course2); err != nil {
		fmt.Printf("Insert(course2), n=%d, err=%v\r\n", n, err)
	}

	// 查询
	// 1.根据user查询course
	var courses []*models.Courses
	if num, err := o.QueryTable("courses").Filter("User", 1).RelatedSel().All(&courses); err != nil {
		fmt.Printf("根据user查询course, num=%d, err=%v\r\n", num, err)
	}
	fmt.Println("根据user查询course:", courses[0].Name, courses[1].Score)

	// 2.根据course查user
	var user models.User
	if err := o.QueryTable("user").Filter("Course__Name", "数学").RelatedSel().One(&user); err != nil {
		fmt.Printf("根据course查user, err=%v\r\n", err)
	}
	fmt.Println("根据course查user:", user)

	// 删除-beego是默认级联删除-所以可以直接删除user
	//o.Delete(course1)
	//o.Delete(course2)
	o.Delete(user1)
	o.Delete(head1)
}

三、多对多

User表和Tags表是多对多关系
User表中:rel(m2m)。Tags表中reverse(many)。设置rel(m2m)会自动生成中间表user_tagss

func (this *UserController) Many2Many() {
	head1 := &models.Head{
		Weight: 11,
		Volume: 32,
	}
	head2 := &models.Head{
		Weight: 33,
		Volume: 88,
	}
	user1 := &models.User{
		Name: "小李飞刀",
		Age: 32,
		Head: head1,
	}
	user2 := &models.User{
		Name: "玉面青龙",
		Age: 18,
		Head: head2,
	}
	tag1 := &models.Tags{
		Name: "帅",
	}
	tag2 := &models.Tags{
		Name: "武功高强",
	}
	// 只是用赋值来关联的话，关系表中不会有值
	//user1.Tags = []*models.Tags{tag1, tag2}
	//user2.Tags = []*models.Tags{tag1, tag2}
	//tag1.User = []*models.User{user1, user2}
	//tag2.User = []*models.User{user1, user2}
	orm.Debug = true
	o := orm.NewOrm()
	// 先得处理one2one的结构，不用处理many2one的结构
	if n, err := o.Insert(head1); err != nil {
		fmt.Printf("Insert(head1), n=%d, err=%v\r\n", n, err)
	}
	if n, err := o.Insert(head2); err != nil {
		fmt.Printf("Insert(head2), n=%d, err=%v\r\n", n, err)
	}
	// 插入user，只插入user没插入tags前，user_tagss表里没有数据
	if n, err := o.Insert(user1); err != nil {
		fmt.Printf("Insert(user1), n=%d, err=%v\r\n", n, err)
	}
	if n, err := o.Insert(user2); err != nil {
		fmt.Printf("Insert(user2), n=%d, err=%v\r\n", n, err)
	}
	// 插入tags
	if n, err := o.Insert(tag1); err != nil {
		fmt.Printf("Insert(tag1), n=%d, err=%v\r\n", n, err)
	}
	if n, err := o.Insert(tag2); err != nil {
		fmt.Printf("Insert(tag2), n=%d, err=%v\r\n", n, err)
	}

	// 添加关联
	m2m1 := o.QueryM2M(user1, "Tags")
	num, err := m2m1.Add(tag1, tag2)
	if err != nil {
		fmt.Printf("user1 m2m.Add, num=%d, err=%v\r\n", num, err)
	}
	fmt.Println("user1 m2m.Add num=", num)

	m2m2 := o.QueryM2M(user2, "Tags")
	num, err = m2m2.Add(tag1, tag2)
	if err != nil {
		fmt.Printf("user2 m2m.Add, num=%d, err=%v\r\n", num, err)
	}
	fmt.Println("user2 m2m.Add num=", num)

	// 删除
	m2m1.Remove(tag1)

	// 多对多查询
	var users []*models.User
	num, err = o.QueryTable("user").Filter("Tags__Tags__Name", "帅").All(&users)
	if err != nil {
		fmt.Printf("多对多查询, user num=%d, err=%v\r\n", num, err)
	}
	fmt.Printf("many2many user num=%d, users=%v\r\n", num, users)
	for key, val := range users {
		fmt.Printf("users%d name=%v\n", key, val.Name)
	}

	var tags []*models.Tags
	num, err = o.QueryTable("tags").Filter("User__User__Age", 18).All(&tags)
	if err != nil {
		fmt.Printf("多对多查询, tags num=%d, err=%v\r\n", num, err)
	}
	fmt.Printf("many2many tags num=%d, tags=%v\r\n", num, tags)
	for key, val := range tags {
		fmt.Printf("tags%d name=%v\n", key, val.Name)
	}

}