17.获取帖子列表

一、建立帖子表结构并插入一些测试数据

create table post
(
    id           bigint auto_increment primary key,
    post_id      bigint                              not null comment '帖子id',
    title        varchar(128)                        not null comment '标题',
    content      varchar(8192)                       not null comment '内容',
    author_id    bigint                              not null comment '作者的用户id',
    community_id bigint                              not null comment '所属社区',
    status       tinyint   default 1                 not null comment '帖子状态',
    create_time  timestamp default CURRENT_TIMESTAMP null comment '创建时间',
    update_time  timestamp default CURRENT_TIMESTAMP null on update CURRENT_TIMESTAMP comment '更新时间',
    constraint idx_post_id unique (post_id)
) collate = utf8mb4_general_ci;

create index idx_author_id on post (author_id);

create index idx_community_id on post (community_id);

INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (1, 14283784123846656, '学习使我快乐', '只有学习才能变得更强', 28018727488323585, 1, 1, '2024-03-09 09:58:39', '2024-03-09 09:58:39');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (2, 14373128436191232, 'CSGO开箱子好上瘾', '花了钱不出金，我好气啊', 28018727488323585, 2, 1, '2024-03-09 15:53:40', '2024-03-09 15:53:40');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (3, 14373246019309568, 'IG牛逼', '打得好啊。。。', 28018727488323585, 3, 1, '2024-03-09 15:54:08', '2024-03-09 15:54:08');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (4, 19432670719119360, '投票功能真好玩', '12345', 28018727488323585, 2, 1, '2024-03-23 14:58:29', '2024-03-23 14:58:29');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (5, 19433711036534784, '投票功能真好玩2', '12345', 28018727488323585, 2, 1, '2024-03-23 15:02:37', '2024-03-23 15:02:37');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (6, 19434165682311168, '投票功能真好玩2', '12345', 28018727488323585, 2, 1, '2024-03-23 15:04:26', '2024-03-23 15:04:26');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (7, 21810561880690688, '看图说话', '4321', 28018727488323585, 2, 1, '2024-03-30 04:27:23', '2024-03-30 04:27:23');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (8, 21810685746876416, '永远不要高估自己', '做个普通人也挺难', 28018727488323585, 3, 1, '2024-03-30 04:27:52', '2024-03-30 04:27:52');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (9, 21810865955147776, '你知道泛型是什么吗？', '不知道泛型是什么却一直在问泛型什么时候出', 28018727488323585, 1, 1, '2024-03-30 04:28:35', '2024-03-30 04:28:35');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (10, 21810938202034176, '国庆假期哪里玩？', '走遍四海，还是威海。', 28018727488323585, 1, 1, '2024-03-30 04:28:52', '2024-03-30 04:28:52');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (11, 1, 'test', 'just for test', 1, 1, 1, '2024-03-12 14:03:18', '2024-03-12 14:03:18');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (12, 92636388033302528, 'test', 'just a test', 1, 1, 1, '2024-03-12 15:03:56', '2024-03-12 15:03:56');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (13, 92636388142354432, 'test', 'just a test', 1, 1, 1, '2024-03-12 15:03:56', '2024-03-12 15:03:56');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (15, 123, 'test', 'just a test', 1, 1, 1, '2024-03-13 03:31:50', '2024-03-13 03:31:50');
INSERT INTO post (id, post_id, title, content, author_id, community_id, status, create_time, update_time) VALUES (16, 10, 'test', 'just a test', 123, 1, 1, '2024-03-13 04:12:44', '2024-03-13 04:12:44');

select * from post;

二、通过SQL建立对应的数据模型

由于返回给前端的字段不完全和DB一样了，需要返回作者名字和社区详情，所以我们还需要额外定义一个ApiPostDetail结构体(实际工作中，DB模型，路由request和response应该分别是三个结构体的)

models/post.go

package models

import "time"

type Post struct {
	ID          int64     `gorm:"column:id" db:"id" json:"id" form:"id"`
	PostId      int64     `gorm:"column:post_id" db:"post_id" json:"post_id" form:"post_id"`                     //  帖子id
	Title       string    `gorm:"column:title" db:"title" json:"title" form:"title"`                             //  标题
	Content     string    `gorm:"column:content" db:"content" json:"content" form:"content"`                     //  内容
	AuthorId    int64     `gorm:"column:author_id" db:"author_id" json:"author_id" form:"author_id"`             //  作者的用户id
	CommunityId int64     `gorm:"column:community_id" db:"community_id" json:"community_id" form:"community_id"` //  所属社区
	Status      int64     `gorm:"column:status" db:"status" json:"status" form:"status"`                         //  帖子状态
	CreateTime  time.Time `gorm:"column:create_time" db:"create_time" json:"create_time" form:"create_time"`     //  创建时间
	UpdateTime  time.Time `gorm:"column:update_time" db:"update_time" json:"update_time" form:"update_time"`     //  更新时间
}

func (Post) TableName() string {
	return "post"
}

// 这里将帖子路由需要相关request和response也定义一下
// 因为Post结构中只有社区id和作者id，但是需要展示为作者名字和社区详情
// ApiPostDetail 帖子详情接口的结构体
type ApiPostDetail struct {
	AuthorName string             `json:"author_name"` // 作者
	VoteNum    int64              `json:"vote_num"`    // 投票数
	*Post                         // 嵌入帖子结构体
	*Community `json:"community"` // 嵌入社区信息
}

三、建立路由

router/route.go

	v1.GET("/posts", controller.GetPostListHandler)

四、开发GetPostListHandler

controller/post.go

func GetPostListHandler(c *gin.Context) {
	// 获取分页参数，由于其他handler可能也有获取分页参数的诉求，故封装为函数
	page, size := getPageInfo(c)

	// 从DB获取数据
	data, err := logic.GetPostList(page, size)
	if err != nil {
		zap.L().Error("logic.GetPostList() failed", zap.Error(err))
		ResponseError(c, CodeServerBusy)
		return
	}
	ResponseSuccess(c, data)

}

由于其他handler可能也有获取分页参数的诉求，故封装为函数

controller/request.go

// 获取分页参数
func getPageInfo(c *gin.Context) (int64, int64) {
	pageStr := c.Query("page")
	sizeStr := c.Query("size")

	var (
		page int64
		size int64
		err  error
	)

	page, err = strconv.ParseInt(pageStr, 10, 64)
	if err != nil {
		page = 1 // 默认第一页
	}

	size, err = strconv.ParseInt(sizeStr, 10, 64)
	if err != nil {
		size = 10 // 默认一页10条
	}

	return page, size
}

五、编写logic

logic/post.go

package logic

import (
	"bluebell/dao/mysql"
	"bluebell/models"

	"go.uber.org/zap"
)

// GetPostList 获取帖子列表
func GetPostList(page, size int64) (data []*models.ApiPostDetail, err error) {
	posts, err := mysql.GetPostList(page, size)
	if err != nil {
		zap.L().Error("get post list failed", zap.Error(err))
		return nil, err
	}

	data = make([]*models.ApiPostDetail, 0, len(posts))
	for _, post := range posts {
		// 根据作者id查询作者名字
		user, err := mysql.GetUserById(post.AuthorId)
		if err != nil {
			zap.L().Error("mysql.GetUserById(post.AuthorID) failed",
				zap.Int64("author_id", post.AuthorId),
				zap.Error(err))
			continue
		}

		// 根据社区id查询社区信息
		community, err := mysql.GetCommunityDetailByID(post.CommunityId)
		if err != nil {
			zap.L().Error("mysql.GetCommunityDetailByID(post.CommunityId) failed",
				zap.Int64("community_id", post.CommunityId),
				zap.Error(err))
			continue
		}

		postDetail := &models.ApiPostDetail{
			AuthorName: user.Username,
			VoteNum:    0,
			Post:       post,
			Community:  community,
		}
		data = append(data, postDetail)
	}

	return data, nil
}

六、编写dao层

mysql/post.go：增加获取帖子列表函数

package mysql

import "bluebell/models"

func GetPostList(page, size int64) ([]*models.Post, error) {

	posts := make([]*models.Post, 0)
	err := db.Model(models.Post{}).Offset(int((page - 1) * size)).Limit(int(size)).Find(&posts).Error
	if err != nil {
		return nil, err
	}

	return posts, nil
}

mysql/user.go：增加通过作者ID获取作者详情函数

func GetUserById(id int64) (*models.User, error) {
	user := &models.User{}
	err := db.Where("user_id = ?", id).Find(&user).Error
	return user, err

}

七、编译测试运行