查询多对多表数据

今天golang学习网给大家带来了《查询多对多表数据》，其中涉及到的知识点包括等等，无论你是小白还是老手，都适合看一看哦~有好的建议也欢迎大家在评论留言，若是看完有所收获，也希望大家能多多点赞支持呀！一起加油学习~

我想处理由 gorm db.automigrate(&document{}, &folder{}) 自动生成的表 many2many。

在这种情况下，文件夹有文档，文档有文件夹，到目前为止一切都很好，但关联有状态（有效、无效...），因此我必须查看 documents_folders 表才能了解状态。

数据库：

+-----------------------+
| table                 |
+-----------------------+
| documents             |
| documents_folders     |
| folders               |
+-----------------------+

型号：

东西

（id primary_key == uuid）

type stuff struct {
    id          uuid.uuid  `gorm:"type:char(36);primary_key;" json:"id"`
    createdat   time.time  `json:"created_at"`
    updatedat   time.time  `json:"update_at"`
    deletedat   *time.time `sql:"index" json:"deleted_at"`
}

文件夹

type folder struct {
    stuff
    name      string      `json:"name" form:"name" gorm:"name;"`
    documents []*document `json:"documents" gorm:"many2many:documents_folders;"`
}

文档

type document struct {
    stuff
    name        string    `json:"name" form:"name" gorm:"name;"`
    folders     []*folder `json:"folders" gorm:"many2many:documents_folders;"`
}

文档文件夹

我创建了这个模型来访问表，它是自动生成的，但我必须添加状态列

type documentsfolders struct {
    documentid string    `json:"document_id" sql:"type:char(36) references documents(id)"`
    document   *document `json:"document"`
    folderid   string    `json:"folder_id" sql:"type:char(36) references folders(id)"`
    folder     *folder   `json:"folder"`
    state      string    `json:"state" form:"state" gorm:"state;"`
}

当我预加载 document 和 folder 时，我无法访问它，如下所示：

folder := Folder{}
db.Preload("Documents").Where("id = ?", "myUUID").First(&folder)

我想查看文档关联的状态。

感谢您的帮助

解决方案

您遇到以下问题：

• 您错过了为 documentsfolders 添加 automigrate。
• 不要使用带指针的切片。

查看以下固定示例以获取更多参考：

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "math/rand"
    "time"
)

type UUID = string

type Model struct {
    ID        UUID       `gorm:"type:char(36);primary_key;" json:"id"`
    CreatedAt time.Time  `json:"created_at"`
    UpdatedAt time.Time  `json:"update_at"`
    DeletedAt *time.Time `sql:"index" json:"deleted_at"`
}

type Folder struct {
    Model
    Name      string     `json:"name" form:"name" gorm:"name;"`
    Documents []Document `json:"documents" gorm:"many2many:documents_folders;"`
}

type Document struct {
    Model
    Name    string   `json:"name" form:"name" gorm:"name;"`
    Folders []Folder `json:"folders" gorm:"many2many:documents_folders;"`
}

type DocumentsFolders struct {
    DocumentID string    `json:"document_id" sql:"type:char(36) REFERENCES documents(id)"`
    Document   *Document `json:"document"`
    FolderID   string    `json:"folder_id" sql:"type:char(36) REFERENCES folders(id)"`
    Folder     *Folder   `json:"folder"`
    State      string    `json:"state" form:"state" gorm:"state;"`
}

var i = rand.Int()

func genId() string {
    id := i
    i++
    return fmt.Sprintf("%036d", id)
}

func main() {
    db, _ := gorm.Open("sqlite3", "./db.sqlite")
    db.AutoMigrate(&Document{}, &Folder{}, &DocumentsFolders{})

    folder1 := &Folder{
        Model:     Model{ID: genId()},
        Name:      "myFolder",
        Documents: nil,
    }
    db.FirstOrCreate(&folder1, folder1.ID)

    doc1 := &Document{
        Model:   Model{ID: genId()},
        Name:    "doc1",
    }
    db.FirstOrCreate(doc1, doc1.ID)

    doc2 := &Document{
        Model:   Model{ID: genId()},
        Name:    "doc2",
    }
    db.FirstOrCreate(doc2, doc2.ID)

    // join them
    db.Create(&DocumentsFolders{
        DocumentID: doc1.ID,
        FolderID:   folder1.ID,
        State:      "happy!",
    })
    db.Create(&DocumentsFolders{
        DocumentID: doc2.ID,
        FolderID:   folder1.ID,
        State:      "happy!",
    })

    // get the new folder to check
    db.Preload("Documents").First(folder1, folder1.ID)

    // two documents are printed
    fmt.Printf("Documents count: %d\n", len(folder1.Documents)) // prints 2
}

理论要掌握，实操不能落！以上关于《查询多对多表数据》的详细介绍，大家都掌握了吧！如果想要继续提升自己的能力，那么就来关注golang学习网公众号吧！