文章目录
- 一、建立帖子表结构并插入一些测试数据
- 二、通过SQL建立对应的数据模型
- 三、建立路由
- 四、开发GetPostListHandler
- 五、编写logic
- 六、编写dao层
- 七、编译测试运行
一、建立帖子表结构并插入一些测试数据
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
}
七、编译测试运行
