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

一、表结构

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;

二、回复列表嵌套节点 

1. 新增给评论类型回复的数据(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 int64
	err := db.Model(tb).Debug().Select("COALESCE(MAX(`_rgt`), 0)").Scan(&maxRight).Error
	if err != nil {
		return err
	}
	tb.Left = maxRight + 1
	tb.Right = maxRight + 2
	tb.ParentId = nil
	return 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 >= 5

UPDATE `reply` SET `_lft`=_lft + 2,`updated_at`='2024-09-04 16:58:53.732' WHERE _lft > 5

INSERT 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;
SELECT
	r.* 
FROM
	(
	SELECT
		replies.*,
		@row_number :=
	IF
		( @prev_comment_id = replies.comment_id, @row_number + 1, 1 ) AS row_num,
		@prev_comment_id := replies.comment_id 
	FROM
		`replies` 
	WHERE
		comment_id IN ( 32405, 32401, 32404) 
		AND `replies`.`deleted_at` IS NULL 
	ORDER BY
		replies.comment_id DESC, FIELD(id, 30254,30256) DESC,_lft ASC 
	) AS r 
WHERE
	r.row_num <= 3;

GORM:

var repliesFieldClause string
	replies := make([]*model.Replies, 0)
	err = mysql.DefaultMysql.Db.Connection(func(tx *gorm.DB) (err error) {
		err = tx.Exec("SET @row_number = 0").Error
		if err != nil {
			return
		}
		err = tx.Exec("SET @prev_comment_id = NULL").Error
		if 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
	})