当前位置：首页 > news >正文

获取每个评论下的回复列表[支持定位指定回复ID](基本思路+Go-Gorm实现示例)

news 来源：原创 2024/9/20 18:31:03

一、表结构

CREATE TABLE `comments` (`id` int unsigned NOT NULL AUTO_INCREMENT,`publish_id` int NOT NULL COMMENT '文章/动态id',`from_uid` int NOT NULL COMMENT '评论者id',`content` text CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL COMMENT '内容',`ip_address` varchar(15) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`ip_place` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL,`deleted_at` timestamp NULL DEFAULT NULL,`created_at` timestamp NULL DEFAULT NULL,`updated_at` timestamp NULL DEFAULT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC COMMENT='评论表';

CREATE TABLE `reply` (`id` int unsigned NOT NULL AUTO_INCREMENT,`name` varchar(255) NOT NULL DEFAULT '',`comment_id` int unsigned DEFAULT NULL,`parent_id` int unsigned DEFAULT NULL,`_lft` int DEFAULT NULL,`_rgt` int DEFAULT NULL,`created_at` datetime NOT NULL,`updated_at` datetime NOT NULL,PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

二、回复列表嵌套节点

新增给评论类型回复的数据(replies.reply_type="comment"):

SQL:

// 获取replies表内最大_rgt值
select COALESCE(MAX(`_rgt`), 0) from replies;// 为评论ID为1001下插入一条新的replies数据
INSERT INTO `reply` (`name`,`comment_id`,`parent_id`,`_lft`,`_rgt`,`created_at`,`updated_at`) VALUES ('节点1-1-1',1001,NULL,15,16,'2024-09-04 16:50:53.415','20209-04 16:50:53.415')

GORM:

func (tb *Reply) InsertNode() error {var maxRight int64err := db.Model(tb).Debug().Select("COALESCE(MAX(`_rgt`), 0)").Scan(&maxRight).Errorif err != nil {return err}tb.Left = maxRight + 1tb.Right = maxRight + 2tb.ParentId = nilreturn db.Debug().Create(tb).Error
}

2. 新增给回复类型回复的数据(replies.reply_type="reply"):

SQL:

UPDATE `reply` SET `_rgt`=_rgt + 2,`updated_at`='2024-09-04 16:58:53.728' WHERE _rgt >= 5UPDATE `reply` SET `_lft`=_lft + 2,`updated_at`='2024-09-04 16:58:53.732' WHERE _lft > 5INSERT INTO `reply` (`name`,`parent_id`,`_lft`,`_rgt`,`created_at`,`updated_at`) VALUES ('节点1-1-1',7,5,6,'2024-09-04 16:58:53.733','2024-09- 16:58:53.733')

GORM:

func (tb *Reply) AppendNode(name string) error {// 获取当前父节点的right边界值parentNodeRight := tb.Right// ps: 以下两步为了给新节点腾出空间// 更新所有右值大于或等于父节点右值的节点db.Model(&Reply{}).Debug().Where("_rgt >= ?", parentNodeRight).Update("_rgt", gorm.Expr("_rgt + 2"))// 更新左值大于父节点右值的节点db.Model(&Reply{}).Debug().Where("_lft > ?", parentNodeRight).Update("_lft", gorm.Expr("_lft + 2"))// 插入新的子节点newNode := Reply{Name:     name,ParentId: &tb.Id,Left:     parentNodeRight,Right:    parentNodeRight + 1,}return db.Debug().Create(&newNode).Error
}

3. 获取回复嵌套列表:

GORM:

type Reply struct {Id        int64     `json:"id"`Name      string    `json:"name"`ParentId  *int64    `json:"parent_id"`Left      int64     `json:"left" gorm:"column:_lft"`Right     int64     `json:"right" gorm:"column:_rgt"`Children  []*Reply  `json:"children" gorm:"-"`CreatedAt time.Time `json:"created_at"`UpdatedAt time.Time `json:"updated_at"`
}func GetToTree() (reply []*Reply, err error) {nodes := make([]*Reply, 0)if err = db.Order("_lft").Find(&nodes).Error; err != nil {return}nodeMap := make(map[int64]*Reply, len(nodes))reply = make([]*Reply, 0)// 构建节点映射for _, node := range nodes {nodeMap[node.Id] = node}for _, node := range nodes {if node.ParentId == nil {reply = append(reply, node)} else if parent, exists := nodeMap[*node.ParentId]; exists {parent.Children = append(parent.Children, node)}}return
}

三、展示评论列表包含回复

SQL:


SET @row_number = 0;SET @prev_comment_id = NULL;
SELECTr.* 
FROM(SELECTreplies.*,@row_number :=IF( @prev_comment_id = replies.comment_id, @row_number + 1, 1 ) AS row_num,@prev_comment_id := replies.comment_id FROM`replies` WHEREcomment_id IN ( 32405, 32401, 32404) AND `replies`.`deleted_at` IS NULL ORDER BYreplies.comment_id DESC, FIELD(id, 30254,30256) DESC,_lft ASC ) AS r 
WHEREr.row_num <= 3;

GORM:

var repliesFieldClause stringreplies := make([]*model.Replies, 0)err = mysql.DefaultMysql.Db.Connection(func(tx *gorm.DB) (err error) {err = tx.Exec("SET @row_number = 0").Errorif err != nil {return}err = tx.Exec("SET @prev_comment_id = NULL").Errorif err != nil {return}repliesQuery := mysql.DefaultMysql.Db.Model(&model.Replies{}).Where("comment_id IN (?)", commentIDs).Select("replies.*, @row_number := IF(@prev_comment_id = replies.comment_id, @row_number + 1, 1) AS row_num, @prev_comment_id := replies.comment_id")sortReplyIDsLen := len(sortReplyIDs)if sortReplyIDsLen > 0 {sortReplyIdString := tool.IntSliceToString(sortReplyIDs)repliesFieldClause = fmt.Sprintf("replies.comment_id DESC, FIELD(id, %s) DESC, _lft ASC", sortReplyIdString)replyLimit = sortReplyIDsLen} else {repliesFieldClause = fmt.Sprintf("replies.comment_id DESC, _lft ASC")}repliesQuery.Order(repliesFieldClause)repliesSubQuery := tx.Table("(?) AS r", repliesQuery).Where("r.row_num <= ?", replyLimit).Select("r.*").Unscoped().Find(&replies)if err = repliesSubQuery.Error; err != nil {return}return})