Gorm - 根据需要预加载深度

Question

我正在使用 Gorm，对如何从模型中检索嵌套的 SubComments 有一些疑问。我遇到的问题是评论嵌套了两层深，即 Comment.SubComments 没有加载。我是否遗漏了 Preload 的内容？

我还认为我需要在 Comment of foreignKey:parent_id,parent_type 上使用复合外键，但这不起作用。

https://goplay.tools/snippet/kOhjUs7X6NQ

这是 asteriskdev 的另一个尝试:

https://goplay.tools/snippet/jUu_W8B4cg-

您需要在本地运行代码，因为 playground 不支持 sqlite 数据库。

package main

import (
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
)

type BlogPost struct {
    ID       uint `gorm:"primary_key"`
    Content  string
    Comments []Comment `gorm:"foreignKey:parent_id;references:id"`
}

type ParentType int

const (
    PT_BlogPost ParentType = 1
    PT_Comment             = 2
)

type Comment struct {
    ID          uint `gorm:"primary_key"`
    ParentId    uint
    ParentType  ParentType
    Comment     string
    // TODO composite foreign key not working
    SubComments []Comment `gorm:"foreignKey:parent_id,parent_type;references:id"`
}

func createComment(parentId uint, parentType ParentType) {
    switch parentType {
    case PT_BlogPost:
        var blogPost BlogPost
        // lookup blog post
        if err := models.DB.Where("id = ?", parentId).First(&blogPost).Error; err != nil {
            return
        }
        comment := Comment{
            ParentId:    parentId,
            ParentType:  PT_BlogPost,
            Comment:     "",
            SubComments: nil,
        }
        models.DB.Create(&comment)

        models.DB.Model(&blogPost).Updates(&BlogPost{
            Comments: append(blogPost.Comments, comment),
        })

    case models.PT_Comment:
        var parentComment Comment
        // lookup comment
        if err := models.DB.Where("id = ?", parentId).First(&parentComment).Error; err != nil {
            return
        }
        // Create comment and add comment to db
        comment := Comment{
            ParentId:    parentComment.ID,
            ParentType:  models.PT_Comment,
            Comment:     "",
            SubComments: nil,
        }
        models.DB.Create(&comment)
        // Append to Parent Comment and persist Parent Comment
        models.DB.Session(&gorm.Session{FullSaveAssociations: true}).Model(&parentComment).Updates(&Comment{
            SubComments: append(parentComment.SubComments, comment),
        })
    }
}

func GetCommentsForBlogPost(blogPostId uint) {
    var comments []Comment

    // Lookup Comments by BlogPostId
    **// TODO Problem is it is not returning all nested comments**
    **// i.e. The Comments.SubComments**
    if err := models.DB.Preload(clause.Associations).
        Where(&Comment{ParentType: PT_BlogPost, ParentId: blogPostId}).
        Find(&comments).Error; err != nil {
        return
    }
}

尝试在 ParentId 和 ParentType 上创建索引并将其设置为外键也不起作用:

type Comment struct {
    ID          uint `gorm:"primary_key"`
    ParentId    uint `gorm:"index:idx_parent"`
    ParentType  ParentType `gorm:"index:idx_parent"`
    Comment     string
    // TODO composite foreign key not working
    SubComments []Comment `gorm:"foreignKey:idx_parent"`
}

我在下面的注释行中收到错误消息:为 struct Comment 的字段 SubComments 找到无效字段:为关系定义有效的外键或实现 Valuer/Scanner 接口(interface)

type CreateBlogPostInput struct {
    Title   string `json:"title" binding:"required"`
    Content string `json:"content" binding:"required"`
}

func CreateBlogPost(input CreateBlogPostInput) {
    var input CreateBlogPostInput

    // Create blog post
    blogPost := models.BlogPost{
        Title:    input.Title,
        Content:  input.Content,
        Comments: []models.Comment{},
    }

    // ***Foreign key error here***
    models.DB.Create(&blogPost)
}

Answer 1

编辑:

这就是我想出的方法，可以让您朝着正确的方向前进。现在我已经坐下来，我不知道预加载是否是你想要的。这取决于你需要走多少层，天气会不会开始变得麻烦。您不需要任何复合键，甚至不需要显式声明关系。这是使用 GORM 命名约定编写的方式，可以推断出这些关系。

使用纯 go sqlite 驱动程序并使用 GORM 的记录器

import (
    "errors"
    "log"
    "strings"
    "time"

    "github.com/glebarez/sqlite" // Use the pure go sqlite driver
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "gorm.io/gorm/logger" // you need a logger for gorm
)

您的实现开启了 ParentType，所以我保留了它。

type ParentType int

const (
    PT_BlogPost ParentType = iota
    PT_Comment
)

您需要实时数据库连接。看有没有返回，没有创建返回。

var dB *gorm.DB

// DB returns a live database connection
func DB() *gorm.DB {
    var database *gorm.DB
    var err error
    if dB == nil {
        database, err = gorm.Open(sqlite.Open("test.db"), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info), // you need a logger if you are going to do this.
            NowFunc: func() time.Time {
                return time.Now().Local() // timestamps
            },
        })
        if err != nil {
            panic("Failed to connect to database!")

        }
        dB = database
    }
    return dB

}

因为 BlogPost 有一个 Comments []Comment 字段而 Comment 有一个 BlogPostID 字段 GORM 推断出这种关系.一个 BlogPost 可以有多个 Comment 一个 Comment 可以有多个 SubComment。

// BlogPost describes a blog post
type BlogPost struct {
    ID       uint `gorm:"primaryKey"`
    Title    string
    Content  string
    Comments []Comment
}

Comment 可以有一个 BlogPostID，它指的是与其相关联的 BlogPost。它也可以有一个 CommentID。 GORM 推断出这种关系。 GORM 将 BlogPostID 读取为 BlogPost.ID。 CommentID 作为 Comment.ID。 TopLevelID 将包含最上面的 Comment ID。如果 Comment 是顶级 Comment，TopLevelID 将包含它自己的 ID。这里的想法是每个 Comment 都知道其顶级 Comment 的 ID。

// associations are applied due to the naming convention eg. BlogPostID refers to BlogPost.ID, CommentID refers to Comment.ID
type Comment struct {
    ID                uint `gorm:"primaryKey"`
    Comment           string
    BlogPostID        *uint // if this is attached to a blogpost, the blogpost ID will be here otherwise nil
    Depth             uint
    TopLevelCommentID uint
    CommentID         *uint // if this is attached to a comment, the comment ID will be here otherwise nil
    SubComments       []Comment // SubComments will be here based on BlogPostID or CommentID if SubComments are preloaded
}

新BlogPost的构造函数

// NewBlogPost instantiates and returns a *BlogPost
func NewBlogPost(title, content string) *BlogPost {
    return &BlogPost{
        Title:   title,
        Content: content,
    }
}

创建评论在这里，您必须在更新之前保存，以便知道Comment 的ID 是什么来设置TopLevelCommentID。此函数将处理 Comment 与 BlogPost 或其他 Comment 的关联，具体取决于它附加到哪个父级。每个评论都知道它的深度。每次创建新评论时，将具有最高深度的评论添加到顶层 Comment 的 Depth Depth 用于确定添加到 Preload() GORM 方法的 ".SubComment" 的数量。

// New comment instantiates and returns a new comment associated with its parent
func NewComment(parentId uint, parentType ParentType, comment string) (*Comment, error) {
    // Create comment
    c := Comment{
        BlogPostID:  &parentId,
        Comment:     comment,
        SubComments: nil,
    }

    switch parentType {
    case PT_BlogPost:
        blogPost := BlogPost{}
        // lookup blog post
        if err := DB().Preload("Comments").Preload(clause.Associations).Where("id = ?", parentId).First(&blogPost).Error; err != nil {
            return nil, err
        }
        blogPost.Comments = append(blogPost.Comments, c)
        DB().Session(&gorm.Session{FullSaveAssociations: true}).Save(&c)
        DB().Model(&c).UpdateColumn("top_level_comment_id", c.ID)
        blogPost.Comments = append(blogPost.Comments, c)

        return &c, nil

    case PT_Comment:
        parentComment := Comment{}
        // lookup comment
        if err := DB().Preload("SubComments").Preload(clause.Associations).Where("id = ?", parentId).First(&parentComment).Error; err != nil {
            return nil, err
        }

        topComment := Comment{}
        if err := DB().Where("id = ?", parentComment.TopLevelCommentID).First(&topComment).Error; err != nil {
            return nil, err
        }
        topComment.Depth++
        if err := DB().Model(&topComment).UpdateColumn("depth", topComment.Depth).First(&topComment).Error; err != nil {
            return nil, err
        }

        // Create comment and add comment to db
        c.TopLevelCommentID = parentComment.TopLevelCommentID
        parentComment.SubComments = append(parentComment.SubComments, c)

        return &parentComment.SubComments[len(parentComment.SubComments)-1], nil

    }
    return nil, errors.New("fell through parent type switch")
}

这些是完成保存到数据库的方法。

// Create inserts the record in the DB
func (bp *BlogPost) Create() (*BlogPost, error) {

    if err := DB().Create(&bp).Error; err != nil {
        return &BlogPost{}, err
    }
    return bp, nil
}

// Save saves a parent comment and all child associations to the database
func (c *Comment) Save() (*Comment, error) {
    if err := DB().Session(&gorm.Session{FullSaveAssociations: true}).Save(c).Error; err != nil {
        return c, errors.New("error saving comment to db")
    }
    return c, nil

}

创建一个 BlogPost 和 7 个嵌套的 Comment。递归地遍历它们并打印出你找到的每个 Comment。

func main() {

    DB().AutoMigrate(&BlogPost{})
    DB().AutoMigrate(&Comment{})
    // blogpost
    bp, err := NewBlogPost("BlogPostTitle", "BlogPostContent").Create()
    if err != nil {
        log.Println("error creating blogpost", err)
    }
    // top level comment under blogpost
    c, err := NewComment(bp.ID, PT_BlogPost, "top level comment")
    if err != nil {
        log.Println("error creating top level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating top level comment", err)
    }

    c, err = NewComment(c.ID, PT_Comment, "second level comment")
    if err != nil {
        log.Println("error creating second level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating second level comment", err)
    }

    c, err = NewComment(c.ID, PT_Comment, "third level comment")
    if err != nil {
        log.Println("error creating third level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating third level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "fourth level comment")
    if err != nil {
        log.Println("error creating fourth level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating fourth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "fifth level comment")
    if err != nil {
        log.Println("error creating fifth level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating fifth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "sixth level comment")
    if err != nil {
        log.Println("error creating sixth level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating sixth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "seventh level comment")
    if err != nil {
        log.Println("error creating seventh level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating seventh level comment", err)
    }

    for _, v := range bp.GetComments() {
        commentID := uint(0)
        blogPostID := uint(0)
        if v.BlogPostID != nil {
            blogPostID = *v.BlogPostID
        }
        if v.CommentID != nil {
            commentID = *v.CommentID
        }
        log.Printf("\n\nID: %d\nComment: %s\nCommentID: %d\nBlogpostID: %d\n", v.ID, v.Comment, commentID, blogPostID)
        if v.SubComments != nil {
            subComments := v.SubComments
        nextLevel:
            for _, v := range subComments {
                commentID := uint(0)
                blogPostID := uint(0)
                if v.BlogPostID != nil {
                    blogPostID = *v.BlogPostID
                }
                if v.CommentID != nil {
                    commentID = *v.CommentID
                }
                log.Printf("\n\nID: %d\nComment: %s\nCommentID: %d\nBlogpostID: %d\n", v.ID, v.Comment, commentID, blogPostID)
                if v.SubComments != nil {
                    subComments = v.SubComments
                    goto nextLevel // I use gotos to indicate recursion
                }

            }
        }
    }

}

当然这只是一个示例，希望对您有所帮助。

完整源代码:

package main

import (
    "errors"
    "log"
    "strings"
    "time"

    "github.com/glebarez/sqlite"
    "gorm.io/gorm"
    "gorm.io/gorm/clause"
    "gorm.io/gorm/logger"
)

type ParentType int

const (
    PT_BlogPost ParentType = iota
    PT_Comment
)

var dB *gorm.DB

// DB returns a live database connection
func DB() *gorm.DB {
    var database *gorm.DB
    var err error
    if dB == nil {
        database, err = gorm.Open(sqlite.Open("test.db"), &gorm.Config{
            Logger: logger.Default.LogMode(logger.Info), // you need a logger if you are going to do this.
            NowFunc: func() time.Time {
                return time.Now().Local() // timestamps
            },
        })
        if err != nil {
            panic("Failed to connect to database!")

        }
        dB = database
    }
    return dB

}

// NewBlogPost instantiates and returns a *BlogPost
func NewBlogPost(title, content string) *BlogPost {
    return &BlogPost{
        Title:   title,
        Content: content,
    }
}

// BlogPost describes a blog post
type BlogPost struct {
    ID       uint `gorm:"primaryKey"`
    Title    string
    Content  string
    Comments []Comment
}

// Create inserts the record in the DB
func (bp *BlogPost) Create() (*BlogPost, error) {

    if err := DB().Create(&bp).Error; err != nil {
        return &BlogPost{}, err
    }
    return bp, nil
}

// GetComments retrieves comments and sub comments associated with a BlogPost
func (bp *BlogPost) GetComments() []Comment {
    blogPost := BlogPost{}
    sb := strings.Builder{}
    sb.WriteString("Comments.SubComments")

    err := DB().Preload("Comments").Find(&blogPost).Error
    if err != gorm.ErrRecordNotFound {
        Depth := uint(0)
        for _, c := range blogPost.Comments {
            if c.Depth > Depth {
                Depth = c.Depth
            }
        }
        i := uint(1)
        for i < Depth {
            sb.WriteString(".SubComments")
            i++
        }
    }
    DB().
        Preload(sb.String()).         // you may want to reconsider doing it with preloads. 
        Preload(clause.Associations). // also, you will accumulate tech debt as these structures get larger.
        First(&blogPost) // .Error

    return blogPost.Comments

}

// New comment instantiates and returns a new comment associated with its parent
func NewComment(parentId uint, parentType ParentType, comment string) (*Comment, error) {
    // Create comment
    c := Comment{
        BlogPostID:  &parentId,
        Comment:     comment,
        SubComments: nil,
    }

    switch parentType {
    case PT_BlogPost:
        blogPost := BlogPost{}
        // lookup blog post
        if err := DB().Preload("Comments").Preload(clause.Associations).Where("id = ?", parentId).First(&blogPost).Error; err != nil {
            return nil, err
        }
        blogPost.Comments = append(blogPost.Comments, c)
        DB().Session(&gorm.Session{FullSaveAssociations: true}).Save(&c)
        DB().Model(&c).UpdateColumn("top_level_comment_id", c.ID)
        blogPost.Comments = append(blogPost.Comments, c)

        return &c, nil

    case PT_Comment:
        parentComment := Comment{}
        // lookup comment
        if err := DB().Preload("SubComments").Preload(clause.Associations).Where("id = ?", parentId).First(&parentComment).Error; err != nil {
            return nil, err
        }

        topComment := Comment{}
        if err := DB().Where("id = ?", parentComment.TopLevelCommentID).First(&topComment).Error; err != nil {
            return nil, err
        }
        topComment.Depth++
        if err := DB().Model(&topComment).UpdateColumn("depth", topComment.Depth).First(&topComment).Error; err != nil {
            return nil, err
        }

        // Create comment and add comment to db
        c = Comment{
            CommentID:   &parentId,
            Comment:     comment,
            SubComments: nil,
        }
        c.TopLevelCommentID = parentComment.TopLevelCommentID
        parentComment.SubComments = append(parentComment.SubComments, c)

        return &parentComment.SubComments[len(parentComment.SubComments)-1], nil

    }
    return nil, errors.New("fell through parent type switch")
}

// associations are applied due to the naming convention eg. BlogPostID refers to BlogPost.ID, CommentID refers to Comment.ID
type Comment struct {
    ID                uint `gorm:"primaryKey"`
    Comment           string
    BlogPostID        *uint // if this is attached to a blogpost, the blogpost ID will be here otherwise nil
    Depth             uint
    TopLevelCommentID uint
    CommentID         *uint // if this is attached to a comment, the comment ID will be here otherwise nil
    SubComments       []Comment // SubComments will be here based on BlogPostID or CommentID if SubComments are preloaded
}

// Save saves a parent comment and all child associations to the database
func (c *Comment) Save() (*Comment, error) {
    if err := DB().Session(&gorm.Session{FullSaveAssociations: true}).Save(c).Error; err != nil {
        return c, errors.New("error saving comment to db")
    }
    return c, nil

}

func main() {

    DB().AutoMigrate(&BlogPost{})
    DB().AutoMigrate(&Comment{})
    // blogpost
    bp, err := NewBlogPost("BlogPostTitle", "BlogPostContent").Create()
    if err != nil {
        log.Println("error creating blogpost", err)
    }
    // top level comment under blogpost
    c, err := NewComment(bp.ID, PT_BlogPost, "top level comment")
    if err != nil {
        log.Println("error creating top level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating top level comment", err)
    }

    c, err = NewComment(c.ID, PT_Comment, "second level comment")
    if err != nil {
        log.Println("error creating second level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating second level comment", err)
    }

    c, err = NewComment(c.ID, PT_Comment, "third level comment")
    if err != nil {
        log.Println("error creating third level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating third level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "fourth level comment")
    if err != nil {
        log.Println("error creating fourth level comment", err)
    }
    c, err = c.Save()
    if err != nil {
        log.Println("error creating fourth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "fifth level comment")
    if err != nil {
        log.Println("error creating fifth level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating fifth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "sixth level comment")
    if err != nil {
        log.Println("error creating sixth level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating sixth level comment", err)
    }
    c, err = NewComment(c.ID, PT_Comment, "seventh level comment")
    if err != nil {
        log.Println("error creating seventh level comment", err)
    }
    _, err = c.Save()
    if err != nil {
        log.Println("error creating seventh level comment", err)
    }

    for _, v := range bp.GetComments() {
        commentID := uint(0)
        blogPostID := uint(0)
        if v.BlogPostID != nil {
            blogPostID = *v.BlogPostID
        }
        if v.CommentID != nil {
            commentID = *v.CommentID
        }
        log.Printf("\n\nID: %d\nComment: %s\nCommentID: %d\nBlogpostID: %d\n", v.ID, v.Comment, commentID, blogPostID)
        if v.SubComments != nil {
            subComments := v.SubComments
        nextLevel:
            for _, v := range subComments {
                commentID := uint(0)
                blogPostID := uint(0)
                if v.BlogPostID != nil {
                    blogPostID = *v.BlogPostID
                }
                if v.CommentID != nil {
                    commentID = *v.CommentID
                }
                log.Printf("\n\nID: %d\nComment: %s\nCommentID: %d\nBlogpostID: %d\n", v.ID, v.Comment, commentID, blogPostID)
                if v.SubComments != nil {
                    subComments = v.SubComments
                    goto nextLevel // I use gotos to indicate recursion
                }

            }
        }
    }

}