go - 如何使用 GORM 对规范化表进行正确的模型关联？

Question

假设我有一些以这种方式标准化的用户权限表：

这三个表的模型将如下所示：

type Role struct {
    RoleID      string             `gorm:"type:varchar(25); primary_key" json:"id"`
    Name        string             `gorm:"type:varchar(50)" json:"name"`
    Description string             `gorm:"type:text(250)" json:"description"`
    CreatedAt   string             `gorm:"type:timestamp" json:"createdAt"`
    Permission  []PermissionToRole `gorm:"foreignkey:RoleID; association_foreignkey:RoleID" json:"permissions"`
}

type Permission struct {
    PermID      string `json:"id" gorm:"column:permission_id; type:varchar(25); primary_key"`
    Name        string `json:"name" gorm:"type:varchar(50)"`
    Description string `json:"description" gorm:"text(250)"`
}

type PermissionToRole struct {
    RoleID     string     `json:"-" gorm:"type:varchar(25); primary_key"`
    PermID     string     `json:"-" gorm:"column:permission_id; type:varchar(25); primary_key"`
    Permission Permission `gorm:"foreignkey:PermID; association_foreignkey:PermID" json:"permissions"`
}

但是这个模型的结果并不是我想要的。我希望制作一个权限数组，但这就是它的样子（打印到 JSON 时）：

"permissions": [
    {
        "permissions": {
            "id": "create_user",
            "name": "Create user account",
            "description": "Give the permission to create a user account"
        }  
    }
]

我想要的是：

"permissions": [
    {
        "id": "create_user",
        "name": "Create user account",
        "description": "Give the permission to create a user account"
    }
]

有人对此有答案吗？需要帮助。谢谢你。

标签： gogo-gorm

Answer 1

无需创建PermissionToRole，您可以使用多对多序列化为您想要的 JSON 格式。如果您确定需要定义单独的表，则需要确保列名匹配（连接表列名和引用），这是一个示例：

type Role struct {
    RoleID      string       `gorm:"type:varchar(25); primary_key" json:"id"`
    Name        string       `gorm:"type:varchar(50)" json:"name"`
    Description string       `gorm:"type:text(250)" json:"description"`
    CreatedAt   string       `gorm:"type:timestamp" json:"createdAt"`
    Permissions []Permission `gorm:"many2many:permission_to_roles;association_foreignkey:perm_id;foreignkey:role_id;association_jointable_foreignkey:perm_id;jointable_foreignkey:role_id;" json:"permissions"`
}

type Permission struct {
    PermID      string `json:"id" gorm:"type:varchar(25); primary_key"`
    Name        string `json:"name" gorm:"type:varchar(50)"`
    Description string `json:"description" gorm:"text(250)"`
}

type PermissionToRole struct {
    RoleID string `json:"-" gorm:"type:varchar(25); primary_key"`
    PermID string `json:"-" gorm:"type:varchar(25); primary_key"`
}

以及使用它的完整代码：

package main

import (
    "fmt"
    "github.com/jinzhu/gorm"
    _ "github.com/jinzhu/gorm/dialects/sqlite"
    "log"
    "os"
)

type Role struct {
    RoleID      string       `gorm:"type:varchar(25); primary_key" json:"id"`
    Name        string       `gorm:"type:varchar(50)" json:"name"`
    Description string       `gorm:"type:text(250)" json:"description"`
    CreatedAt   string       `gorm:"type:timestamp" json:"createdAt"`
    Permissions []Permission `gorm:"many2many:permission_to_roles;association_foreignkey:perm_id;foreignkey:role_id;association_jointable_foreignkey:perm_id;jointable_foreignkey:role_id;" json:"permissions"`
}

type Permission struct {
    PermID      string `json:"id" gorm:"type:varchar(25); primary_key"`
    Name        string `json:"name" gorm:"type:varchar(50)"`
    Description string `json:"description" gorm:"text(250)"`
}

type PermissionToRole struct {
    RoleID string `json:"-" gorm:"type:varchar(25); primary_key"`
    PermID string `json:"-" gorm:"type:varchar(25); primary_key"`
}

func sample() error {
    _ = os.Remove("test.db") // Remove file to make sure DB is empty
    db, err := gorm.Open("sqlite3", "test.db")
    if err != nil {
        return fmt.Errorf("open DB failed: %w", err)
    }
    defer db.Close()
    db.LogMode(true)

    err = db.AutoMigrate(
        &Role{},
        &Permission{},
    ).Error
    if err != nil {
        return fmt.Errorf("migration failed: %w", err)
    }

    // Put some sample data in DB
    sampleRoles := []Role{
        {RoleID: "role0", Name: "n0"},
        {RoleID: "role1", Name: "n1"},
        {RoleID: "role2", Name: "n2"},
        {RoleID: "role3", Name: "n3"},
    }
    for idx := range sampleRoles {
        err = db.Create(&sampleRoles[idx]).Error
        if err != nil {
            return fmt.Errorf("failed to create: %w", err)
        }
    }

    samplePermissions := []Permission{
        {PermID: "perm0"},
        {PermID: "perm1"},
        {PermID: "perm2"},
        {PermID: "perm3"},
    }
    for idx := range samplePermissions {
        err = db.Create(&samplePermissions[idx]).Error
        if err != nil {
            return fmt.Errorf("failed to create: %w", err)
        }
    }

    sampleM2m := []PermissionToRole{
        {RoleID: "role2", PermID: "perm3"},
        {RoleID: "role2", PermID: "perm1"},
        {RoleID: "role2", PermID: "perm0"},
    }
    for idx := range sampleM2m {
        err = db.Create(&sampleM2m[idx]).Error
        if err != nil {
            return fmt.Errorf("failed to create: %w", err)
        }
    }

    // Do query
    var role Role
    err = db.Preload("Permissions").First(&role, "role_id = ?", "role2").Error
    if err != nil {
        return fmt.Errorf("error in query: %w", err)
    }
    fmt.Printf("%+v\n", role)
    return nil
}

func main() {
    err := sample()
    if err != nil {
        log.Fatal(err)
    }
}