创建点赞模块

like 目录下 like.controller.ts、like.middleware.ts、like.router.ts、like.service.ts
like.controller.ts：

1
import { Request, Response, NextFunction } from "express";

like.middleware.ts：

1
import { Request, Response, NextFunction } from "express";

like.router.ts：

1
2
3
4
5
6
7
8
9
import express from "express";
import * as likeController from "./like.controller";

const router = express.Router();

/**
 * 导出路由
 */
export default router;

like.service.ts：

1
import { connection } from "../app/database/mysql";

创建点赞内容数据表

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `user_like_post`(
  `userId` INT(11) NOT NULL,
  `postId` INT(11) NOT NULL,

  PRIMARY KEY (`userId`,`postId`),

  FOREIGN KEY (`userId`) REFERENCES `user`(`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION,
  FOREIGN KEY (`postId`) REFERENCES `post`(`id`)
    ON DELETE NO ACTION ON UPDATE NO ACTION
) DEFAULT CHARSET=utf8mb4
  COLLATE=utf8mb4_unicode_ci;

定义保存点赞内容的功能

like.service.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
import { connection } from "../app/database/mysql";

/**
 *  保存用户点赞内容
 */
export const createUserLikePost = async (userId: number, postId: number) => {
  // 准备查询
  const statement = `
      INSERT INTO
        user_like_post (userId,postId)
      VALUES (?, ?)
    `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [userId, postId]);

  // 提供数据
  return data;
};

定义点赞内容接口

like.controller.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
import { Request, Response, NextFunction } from "express";
import { createUserLikePost } from "./like.service";

/**
 * 点赞内容
 */
export const storeUserLikePost = async (
  request: Request,
  response: Response,
  next: NextFunction
) => {
  // 准备数据
  const { postId } = request.params;
  const { id: userId } = request.user;

  //   点赞内容
  try {
    const data = await createUserLikePost(userId, parseInt(postId, 10));

    //  做出响应
    response.status(201).send(data);
  } catch (error) {
    next(error);
  }
};

like.router.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
import express from "express";
import * as likeController from "./like.controller";
import { authGuard } from "../auth/auth.middleware";

const router = express.Router();

/**
 * 点赞内容
 */
router.post("/posts/:postId/like", authGuard, likeController.storeUserLikePost);

/**
 * 导出路由
 */
export default router;

定义取消点赞内容功能

like.service.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/**
 *  取消点赞内容
 */
export const deleteUserLikePost = async (userId: number, postId: number) => {
  // 准备查询
  const statement = `
      DELETE FROM user_like_post
      WHERE userId=? AND postId=?
    `;

  // 执行查询
  const [data] = await connection.promise().query(statement, [userId, postId]);

  // 提供数据
  return data;
};

定义取消点赞内容接口

like.controller.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
/**
 *  取消点赞内容接口
 */
export const distroyUserLikePost = async (
  request: Request,
  response: Response,
  next: NextFunction
) => {
  // 准备数据
  const { postId } = request.params;
  const { id: userId } = request.user;

  // 取消点赞内容
  try {
    const data = await deleteUserLikePost(userId, parseInt(postId, 10));

    // 做出响应
    response.send(data);
  } catch (error) {
    next(error);
  }
};

like.router.ts：

1
2
3
4
5
6
7
8
/**
 * 取消点赞内容
 */
router.delete(
  "/posts/:postId/like",
  authGuard,
  likeController.distroyUserLikePost
);

在内容项目中包含点赞数量

定义查询片段 post.provider.ts：

1
2
3
4
5
6
7
totalLikes: `
   (
     SELECT COUNT(user_like_post.postId)
     FROM user_like_post
     WHERE user_like_post.postId = post.id
   ) AS totalLikes
  `

在 post.service.ts 中使用：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
export const getPosts = async (options: GetPostOptions) => {
  const {
    sort,
    filter,
    pagination: { limit, offset },
  } = options;

  // sql参数
  let params: Array<any> = [limit, offset];

  // 设置sql参数
  if (filter.param) {
    params = [filter.param, ...params];
  }

  const statement = `
    SELECT
      post.id,
      post.title,
      post.content,
      ${sqlFragment.user},
      ${sqlFragment.totalComments},
      ${sqlFragment.file},
      ${sqlFragment.tags},
      ${sqlFragment.totalLikes}
    FROM post
    ${sqlFragment.leftJoinUser}
    ${sqlFragment.leftJoinOneFile}
    ${sqlFragment.leftJoinTag}
    WHERE ${filter.sql}
    GROUP BY post.id
    ORDER BY ${sort}
    LIMIT ?
    OFFSET ?
  `;
  const [data] = await connection.promise().query(statement, params);

  return data;
};

用户赞过的内容列表

添加查询片段 post.provider.ts：

1
2
3
4
innerJoinUserLikePost: `
    INNER JOIN user_like_post
      ON user_like_post.postId = post.id
   `,

post.middleware.ts：

1
2
3
4
5
6
7
8
//   过滤出用户赞过的内容
if (user && action == "liked" && !tag) {
  request.filter = {
    name: "userLiked",
    sql: "user_like_post.userId=?",
    param: user,
  };
}

post.service.ts：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
getPosts函数中:

  const statement = `
    SELECT
      post.id,
      post.title,
      post.content,
      ${sqlFragment.user},
      ${sqlFragment.totalComments},
      ${sqlFragment.file},
      ${sqlFragment.tags},
      ${sqlFragment.totalLikes}
    FROM post
    ${sqlFragment.leftJoinUser}
    ${sqlFragment.leftJoinOneFile}
    ${sqlFragment.leftJoinTag}
    ${filter.name == "userLiked" ? sqlFragment.innerJoinUserLikePost : ""} //这里判断一下
    WHERE ${filter.sql}
    GROUP BY post.id
    ORDER BY ${sort}
    LIMIT ?
    OFFSET ?
  `;


getPostsTotalCount函数中

  // 准备查询
  const statement = `
       SELECT
         COUNT(DISTINCT post.id) AS total
       FROM post
       ${sqlFragment.leftJoinUser}
       ${sqlFragment.leftJoinOneFile}
       ${sqlFragment.leftJoinTag}
       ${filter.name == "userLiked" ? sqlFragment.innerJoinUserLikePost : ""}   //这里判断一下
       WHERE ${filter.sql}
     `;