koa接口，项目结尾

这是我参与8月更文挑战的第5天，活动详情查看：8月更文挑战

上一篇文章完成了评论，回复评论，查询评论，删除评论

• 完成创建标签，
• 给动态分配标签
• 上传用户头像
• 上传动态配图

创建标签表，用于多表查询

CREATE TABLE IF NOT EXISTS `label`(
	id INT PRIMARY KEY AUTO_INCREMENT,
	name VARCHAR(10) NOT NULL UNIQUE,
	createAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
	updateAt TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

完成创建标签

//label.router.js

const Router = require('koa-router')

const labelRouter = new Router({prefix: "/label"})

const {
    verifyAuth    //验证用户登录
} = require('../middleware/auth.middleware')

const {
    create
} = require('../controller/label.controller')

labelRouter.post('/', verifyAuth, create)

module.exports = labelRouter

处理数据

//label.controller.js

const service = require('../service/label.service.js')

class labelController {
    async create(ctx, next) {
        const {name} = ctx.request.body

        const result = await service.create(name)

        ctx.body = result
    }
}

module.exports = new labelController()

添加到数据库

//label.service.js

const connection = require("../app/database");

class LabelService {
    async create(name) {
        try {
          const statement = `INSERT INTO label (name) VALUES (?)`;
    
          const [result] = await connection.execute(statement, [name]);
    
          return result;
        } catch (error) {
          console.log(error);
        }
      }
}

module.exports = new LabelService();

测试

给动态分配标签

//moment.router.js

const Router = require('koa-router')

const momentRouter = new Router({prefix: '/moment'})

const {
    verifyAuth,
    verifyPermission
} = require('../middleware/auth.middleware')

const {
    addLabels
} = require('../controller/moment.controller')

const {
    verifyLabelExists
} = require('../middleware/label.middleware')

momentRouter.post("/:momentId/labels", verifyAuth, verifyPermission("moment"), verifyLabelExists, addLabels)

module.exports = momentRouter

判断用户传入的标签是否存在

// label.middleware.js

const service = require('../service/label.service')

const verifyLabelExists = async (ctx, next) => {
    // 取出要添加的标签
    const {labels} = ctx.request.body
    console.log(labels);

    // 判断标签是否存在
    const newLabels = []
    for(let name of labels){
        const labelResult = await service.getLabelByName(name)
        const label = {name}
        if(!labelResult) {
            // 创建标签数据
            const result = await service.create(name)
            label.id = result.insertId
        }else {
            label.id = labelResult.id
        }
        newLabels.push(label)
    }

    ctx.labels = newLabels
    await next()
}

module.exports = {
    verifyLabelExists
}

获取到标签和动态id，传入到数据库处理文件

//moment.controller.js

const momentService = require("../service/moment.service");

class MomentController {
    async addLabels(ctx, next) {
        // 1.获取标签和动态id
        const { labels } = ctx;
        const { momentId } = ctx.params;

        // 2.添加所有的标签
        for (let label of labels) {
          // 2.1.判断标签是否已经和动态有关系
          const isExist = await momentService.hasLabel(momentId, label.id);
          if (!isExist) {
            await momentService.addLabel(momentId, label.id);
          }
        }

        ctx.body = "给动态添加标签成功~";
      }
}

module.exports = new MomentController();

拿到数据，进行处理

//moment.service.js

const connection = require("../app/database");

class MomentService {
    
    //判断该动态是否拥有此标签
    async hasLabel(momentId, labelId) {
        const statement = `
        SELECT * FROM moment_label WHERE moment_id = ? AND label_id = ?
        `;
        const [result] = await connection.execute(statement, [momentId, labelId]);
        return result[0] ? true: false;
      }
    //给动态添加标签
      async addLabel(momentId, labelId) {
        const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (?, ?);`;
        const [result] = await connection.execute(statement, [momentId, labelId]);
        return result;
      }
}

module.exports = new MomentService();

上传头像

//file.router.js

const Router = require('koa-router')

const filRouter = new Router({prefix: '/upload'})

const {
    verifyAuth  //验证登录
} =require('../middleware/auth.middleware')

const {
    avatarHandler
} = require('../middleware/file.middleware')

const {
    saveAvatarInfo
} = require('../controller/file.controller')

//上传头像
filRouter.post('/avatar', verifyAuth, avatarHandler, saveAvatarInfo)

module.exports = filRouter

实现avatarHandler中间件 npm install koa-multer

//file.middleware.js

const Multer = require("koa-multer");

const avatarUpLoad = Multer({
  dest: "./uploads/avatar",   //这个是将上传的图片保存到一个位置
});

const avatarHandler = avatarUpLoad.single("avatar");

module.exports = {
  avatarHandler
};

获取上传图片的信息，然后保存到数据库

//file.controller.js

const fileService = require("../service/file.service");
const userService = require('../service/user.service')
const {APP_HOST, APP_PORT} = require('../app/config')

class FileController {
    async saveAvatarInfo(ctx, next) {
        // 获取图像信息
        const { mimetype, filename, size } = ctx.req.file;
        const { id } = ctx.user;

        // 将图像信息保持到数据库
        const result = await fileService.createAvatar(filename, mimetype, size, id);

        // 将图片地址保存到user中
        const avatarUrl = `${APP_HOST}:${APP_PORT}/users/${id}/avatar`
        console.log(avatarUrl);   //http://localhost:8080/users/用户id/avatar
        await userService.updateAvatarUrlById(avatarUrl, id)

        // 返回结果
        ctx.body = "上传头像成功";
      }
}

module.exports = new FileController();

获取头像

//user.router.js

const Router = require('koa-router')

const userRouter = new Router()

const {
    avatarInfo
} = require("../controller/user.controller")

// 获取头像
userRouter.get('/users/:userId/avatar', avatarInfo)

module.exports = userRouter

设置图像信息

//user.controller.js

const fs = require('fs')

const userService = require("../service/user.service");
const fileService = require("../service/file.service");
const { AVATAR_PATH } = require("../constants/file-path");

class UserController {
  async avatarInfo(ctx, next) {
    // 获取userId
    const { userId } = ctx.params;
    //查询头像
    const avatarInfo = await fileService.getAvatarByUserId(userId);
    console.log(avatarInfo); -->
                `BinaryRow {
                  id: 1,
                  filename: '48b2d8e3740441294e57c91750f59e99',
                  mimetype: 'image/jpeg',
                  size: 98258,
                  user_id: 2,
                  createAt: 2021-08-17T08:11:53.000Z,
                  updateAt: 2021-08-17T08:11:53.000Z
                }`
 
    // 提供图像信息
    ctx.response.set('content-type', avatarInfo.mimetype)  //告诉浏览器，我们这个是图片
    ctx.body = fs.createReadStream(`${AVATAR_PATH}/${avatarInfo.filename}`);
  }
}

module.exports = new UserController();

查询头像

//file.service.js

const connection = require("../app/database");

class FileService {
    async getAvatarByUserId(userId) {
        try {
          const statemnet = `SELECT * FROM avatar WHERE user_id = ?`;

          const [result] = await connection.execute(statemnet, [userId]);

          return result[0];
        } catch (error) {
          console.log(error);
        }
      }
}

module.exports = new FileService();

上传动态配图

//file.router.js

const Router = require('koa-router')

const filRouter = new Router({prefix: '/upload'})

const {
    verifyAuth  //验证登录
} =require('../middleware/auth.middleware')

const {
    pictureHandler
} = require('../middleware/file.middleware')

const {
    savePictureInfo
} = require('../controller/file.controller')

//上传配图
filRouter.post('/picture', verifyAuth, pictureHandler, savePictureInfo)

module.exports = filRouter

获取上传图片

//file.middleware.js

const Multer = require("koa-multer");

const pictureUpload = Multer({
  dest: "./uploads/picture",
});

const pictureHandler = pictureUpload.array("picture", 9);

module.exports = {
  pictureHandler
};

设置图像信息

const fileService = require("../service/file.service")

class FileController {
    async savePictureInfo(ctx, next) {
        // 获取信息
        const files = ctx.req.files
        const {id} = ctx.user
        const {momentId} = ctx.query

        // 保存到数据库
        for(let file of files) {
          const {filename, mimetype, size} = file

          await fileService.createFile(filename, mimetype, size, id, momentId)
        }

        ctx.body = "动态配图上传成功"
      }  
}
module.exports = new FileController();

数据库处理动态配图

//file.service.js

const connection = require("../app/database");

class FileService {
    async createFile(filename, mimetyep, size, userId, momentId) {
    try {
      const statement = `INSERT INTO file (filename, mimetype, size, user_id, moment_id) VALUES (?, ?, ?, ?, ?)`;

      const [result] = await connection.execute(statement, [
        filename,
        mimetyep,
        size,
        userId,
        momentId,
      ]);

      return result;
    } catch (error) {
      console.log(error);
    }
  }
}

module.exports = new FileService();

获取动态配图

//moment.router.js

const Router = require('koa-router')

const momentRouter = new Router({prefix: '/moment'})

const {
    fileInfo
} = require('../controller/moment.controller')

//动态配图
momentRouter.get('/images/:filename', fileInfo)

module.exports = momentRouter

const fs = require("fs");

const fileService = require("../service/file.service");
const momentService = require("../service/moment.service");
const { PICTURE_PATH } = require("../constants/file-path");

class MomentController {
    async fileInfo(ctx, next) {
        try {
          let { filename } = ctx.params;
          
          const fileInfo = await fileService.getFileByFilename(filename);
        
          ctx.response.set("content-type", fileInfo.mimetype);
          
          ctx.body = fs.createReadStream(`./uploads/picture/${filename}`);
        } catch (error) {
          console.log(error);
        }
      }
}

module.exports = new MomentController();

const connection = require("../app/database");

class FileService {
    async getFileByFilename(filename) {
         const statement = `SELECT * FROM file WHERE filename = ?`

         const [result] = await connection.execute(statement, [filename])

         return result[0];
      }
}
module.exports = new FileService();

结尾，我们还需要去修改一下动态的查询，因为用户有了头像，要一起查询

//moment.service.js

const connection = require("../app/database");

class MomentService {
    //这个的单条动态查询
    async getMomentById(id) {
        const statement = `
        SELECT 
          m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
          JSON_OBJECT('id', u.id, 'name', u.name, 'avatarUrl', u.avatar_url) author,
          IF(COUNT(l.id),JSON_ARRAYAGG(
            JSON_OBJECT('id', l.id, 'name', l.name)
          ),NULL) labels,
          (SELECT IF(COUNT(c.id),JSON_ARRAYAGG(
            JSON_OBJECT('id', c.id, 'content', c.content, 'commentId', c.comment_id, 'createTime', c.createAt,
                        'user', JSON_OBJECT('id', cu.id, 'name', cu.name, 'avatarUrl', cu.avatar_url))
          ),NULL) FROM comment c LEFT JOIN user cu ON c.user_id = cu.id WHERE m.id = c.moment_id) comments,
          (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8080/moment/images/', file.filename)) 
          FROM file WHERE m.id = file.moment_id) images
        FROM moment m
        LEFT JOIN user u ON m.user_id = u.id
        LEFT JOIN moment_label ml ON m.id = ml.moment_id
        LEFT JOIN label l ON ml.label_id = l.id
        WHERE m.id = ?
        GROUP BY m.id; 
          `;

        const [result] = await connection.execute(statement, [id]);

        return result[0];
      }
}

module.exports = new MomentService();

//moment.service.js

const connection = require("../app/database");

class MomentService {
    //这个的多条动态查询
    async getMomentList(offset, size) {
    const statement = `
    SELECT 
        m.id id, m.content content, m.createAt createTime, m.updateAt updateTime,
        JSON_OBJECT('id', u.id, 'name', u.name) author,
        (SELECT COUNT(*) FROM comment c WHERE c.moment_id = m.id) commentCount,
        (SELECT COUNT(*) FROM moment_label ml WHERE ml.moment_id = m.id) labelCount,
        (SELECT JSON_ARRAYAGG(CONCAT('http://localhost:8080/moment/images/', file.filename)) 
        FROM file WHERE m.id = file.moment_id) images
      FROM moment m
      LEFT JOIN user u ON m.user_id = u.id
      LIMIT ?, ?;
    `;

    const [result] = await connection.execute(statement, [offset, size]);

    return result;
  }
}

module.exports = new MomentService();

项目地址，有兴趣的小伙伴可以去下载，自己尝试一下哦