GORM-预付尽可能深的预紧力

Question

我正在使用GORM，并有一些问题，即如何从模型中检索嵌套子额度。我得到的问题是注释深嵌套了两个级别，即comment.subcomments没有加载。我是否错过了preload的东西？

我还认为我需要一个复合外键，以评论efirnekey：parent_id，parent_type，但这是行不通的。

https://goplay.tool.tool.tool.tools/snippet/kohjus7x6nq

这是Asteriskdev的另一种尝试。

“ https://goplay.tools/snippet/juu_w8b4cg-” rel =“ nofollow noreferrer”> https://goplay.tool.tools/snippet/juu_w8b4cg-

t支持SQLite DBS。

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和parentype上创建索引，并将其设置为外键也不起作用：

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的字段子节目找到的无效字段：定义关系的有效外国密钥或实现估价师/扫描仪界面

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命名惯例编写的方式，可以推断这种关系。

使用Pure Go Sqlite驱动程序，并使用Logger

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
)

在parthtype上进行GORM进行GORM您的实现开关，因此我将其保留。

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具有注释[]注释字段和注释具有blog postid field gorm侵蚀了关系。一个blogpost可以拥有许多注释一个注释可以具有许多子注释 s。

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

注释可以具有一个blog postID，它指的是与之关联的博客文章。它也可以发表评论。 Gorm渗透了这种关系。 Gorm读取blogpostid AS blogpost.id。 commentid作为comment.id。 Toplevelid将包含顶部注释 id。如果注释是顶级注释，toplevelid将包含其自己的id。这里的想法是每个注释知道其顶级注释's 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,
    }
}

创建评论 的构造函数
在这里，您必须在更新之前保存，才能知道注释>注释的ID是什么是设置toplevelcommentId。此函数将与Blogpost s或其他注释>注释>评论>评论>评论 s的关联 s，具体取决于其附属于哪个父。每个评论都知道其深度。每次创建新的评论时，深度最高的注释都会添加到顶级注释 s 深度 depth用于确定的数量“。子表” s要添加到preload（）Gorm方法中。

// 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个嵌套注释 s。
递归循环并打印出每个注释您找到的。

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
                }

            }
        }
    }

}