Gorm - Preload as deep as necessary

user_417896 0
Viewed 247

I'm using Gorm and have some questions as to how to retrieve nested SubComments from the model. The problem I'm getting is comments nested two levels deep, i.e. the Comment.SubComments are not loading. Am I missing something with the Preload?

I also think I need a composite foreign key on Comment of foreignKey:parent_id,parent_type but thats not working.

https://goplay.tools/snippet/kOhjUs7X6NQ

Here is another attempt by asteriskdev:

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

You will need to run the code locally as the playground doesn't support 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
    }
}

Trying to create an index on ParentId and ParentType and setting that as the foreign key does not work either:

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"`
}

I get an error on the commented line below: invalid field found for struct Comment's field SubComments: define a valid foreign key for relations or implement the 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)
}
go-gorm go
1 Answers

EDIT:

This is what I came up with to get you going in the right direction. Now that I've sat down with it, I don't know if preloading is what you want. It depends on how many levels deep you need to go weather it will start to get cumbersome or not. You don't need any composite keys or to even explicitly declare relationships. The way this is written, using GORM naming convention, the relationships are inferred.

Use the pure go sqlite driver and use a logger for 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
)

Your implementation switches on ParentType so I kept it.

type ParentType int

const (
    PT_BlogPost ParentType = iota
    PT_Comment
)

You need a live database connection. Look to see if there is one and return it, if not create one and return it.

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

}

Because BlogPost has a Comments []Comment field and Comment has a BlogPostID field GORM infers the relationship. One BlogPost can have many Comment One Comment can have many SubComments.

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

A Comment can have a BlogPostID that refers to the BlogPost it is associated with. It can also have a CommentID. GORM infers the relationship. GORM reads BlogPostID as BlogPost.ID. CommentID as Comment.ID. The TopLevelID will contain the top most Comment ID. If the Comment is the top level Comment, TopLevelID will contain its own ID. The idea here is every Comment knows its top level Comment'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
}

Constructor for a new BlogPost

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

Creating a comment Here, you have to save before updating in order to know what the ID of the Comment is to set TopLevelCommentID. This function will handle associating Comments with BlogPosts or other Comments depending on which parent it is being attached to. Every comment knows its Depth. Every time a new comment is created, the comment with the highest depth is added to the top level Comments Depth Depth is used to determine the number of ".SubComment"s to add to the Preload() GORM method.

// 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")
}

These are methods to finalize the save to the database.

// 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

}

Create a BlogPost and 7 nested Comments. Loop over them recursively and print out every Comment you find.

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
                }

            }
        }
    }

}

This of course is only an example, Hope it helps.

Full source code:

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
                }

            }
        }
    }

}
user_185929960
Related