Koa interface, end of project

This is the fifth day of my participation in the August Wen Challenge.More challenges in August

Previous articleFinished the comment, reply to the comment, query the comment, delete the comment

After creating the label,
Assign labels to the dynamic
Uploading user profile pictures
Upload dynamic pictures

Create label tables for multi-table queries

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
);
Copy the code

Finish creating the label

//label.router.js

const Router = require('koa-router')

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

const {
    verifyAuth    // Verify user login
} = require('.. /middleware/auth.middleware')

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

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

module.exports = labelRouter
Copy the code

Process the data

//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()
Copy the code

Add to database

//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();
Copy the code

test

Assign labels to the dynamic

//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
Copy the code

Check whether the label passed by the user exists

// label.middleware.js

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

const verifyLabelExists = async (ctx, next) => {
    // Fetch the label to add
    const {labels} = ctx.request.body
    console.log(labels);

    // Check whether the tag exists
    const newLabels = []
    for(let name of labels){
        const labelResult = await service.getLabelByName(name)
        const label = {name}
        if(! labelResult) {// Create label data
            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
}
Copy the code

The label and dynamic ID are obtained and passed to the database processing file

//moment.controller.js

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

class MomentController {
    async addLabels(ctx, next) {
        // 1. Obtain the label and dynamic ID
        const { labels } = ctx;
        const { momentId } = ctx.params;

        // 2. Add all the labels
        for (let label of labels) {
          // 2.1. Check whether the label has been associated with the dynamic
          const isExist = await momentService.hasLabel(momentId, label.id);
          if(! isExist) {await momentService.addLabel(momentId, label.id);
          }
        }

        ctx.body = "Dynamic label added successfully ~"; }}module.exports = new MomentController();
Copy the code

Get the data, process it

//moment.service.js

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

class MomentService {
    
    // Determine whether the dynamic owns the label
    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;
      }
    // Add dynamic labels
      async addLabel(momentId, labelId) {
        const statement = `INSERT INTO moment_label (moment_id, label_id) VALUES (? ,?) ; `;
        const [result] = await connection.execute(statement, [momentId, labelId]);
        returnresult; }}module.exports = new MomentService();
Copy the code

Upload the picture

//file.router.js

const Router = require('koa-router')

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

const {
    verifyAuth  // Verify login
} =require('.. /middleware/auth.middleware')

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

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

// Upload profile picture
filRouter.post('/avatar', verifyAuth, avatarHandler, saveAvatarInfo)

module.exports = filRouter
Copy the code

implementation`avatarHandler`The middleware`npm install koa-multer`

//file.middleware.js

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

const avatarUpLoad = Multer({
  dest: "./uploads/avatar".// Save the uploaded image to a location
});

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

module.exports = {
  avatarHandler
};
Copy the code

Get the information about the uploaded image and save it to the database

//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) {
        // Get image information
        const { mimetype, filename, size } = ctx.req.file;
        const { id } = ctx.user;

        // Save the image information to the database
        const result = await fileService.createAvatar(filename, mimetype, size, id);

        // Save the image address to user
        const avatarUrl = `${APP_HOST}:${APP_PORT}/users/${id}/avatar`
        console.log(avatarUrl);   / / http://localhost:8080/users/ user id/avatars
        await userService.updateAvatarUrlById(avatarUrl, id)

        // Return the result
        ctx.body = "Avatar uploaded successfully"; }}module.exports = new FileController();
Copy the code

Access to image

//user.router.js

const Router = require('koa-router')

const userRouter = new Router()

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

// Get the avatar
userRouter.get('/users/:userId/avatar', avatarInfo)

module.exports = userRouter
Copy the code

Setting image Information

//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) {
    / / get userId
    const { userId } = ctx.params;
    // Query the profile picture
    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}
 
    // Provide image information
    ctx.response.set('content-type', avatarInfo.mimetype)  // Tell the browser that this is an image
    ctx.body = fs.createReadStream(`${AVATAR_PATH}/${avatarInfo.filename}`); }}module.exports = new UserController();
Copy the code

The query image

//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();
Copy the code

Upload dynamic pictures

//file.router.js

const Router = require('koa-router')

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

const {
    verifyAuth  // Verify login
} =require('.. /middleware/auth.middleware')

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

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

// Upload the image
filRouter.post('/picture', verifyAuth, pictureHandler, savePictureInfo)

module.exports = filRouter
Copy the code

Get uploaded picture

//file.middleware.js

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

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

module.exports = {
  pictureHandler
};
Copy the code

Setting image Information


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

class FileController {
    async savePictureInfo(ctx, next) {
        // Get information
        const files = ctx.req.files
        const {id} = ctx.user
        const {momentId} = ctx.query

        // Save to database
        for(let file of files) {
          const {filename, mimetype, size} = file

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

        ctx.body = "Dynamic illustration uploaded successfully"}}module.exports = new FileController();
Copy the code

The database handles dynamic mapping

//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();
Copy the code

Get dynamic illustrations

//moment.router.js

const Router = require('koa-router')

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

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

// dynamic mapping
momentRouter.get('/images/:filename', fileInfo)

module.exports = momentRouter
Copy the code

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();
Copy the code

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();
Copy the code

Finally, we also need to modify the dynamic query, because users have avatars, they need to query together

//moment.service.js

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

class MomentService {
    // a single dynamic query for this
    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();
Copy the code

//moment.service.js

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

class MomentService {
    // Multiple dynamic queries for this
    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 ? And?; `;

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

    returnresult; }}module.exports = new MomentService();
Copy the code

mo4tech.com (Moment For Technology) is a global community with thousands techies from across the global hang out!Passionate technologists, be it gadget freaks, tech enthusiasts, coders, technopreneurs, or CIOs, you would find them all here.

Previous articleFinished the comment, reply to the comment, query the comment, delete the comment

Create label tables for multi-table queries

Finish creating the label

Process the data

Add to database

test

Assign labels to the dynamic

Check whether the label passed by the user exists

The label and dynamic ID are obtained and passed to the database processing file

Get the data, process it

Upload the picture

implementation`avatarHandler`The middleware`npm install koa-multer`

Get the information about the uploaded image and save it to the database

Access to image

Setting image Information

The query image

Upload dynamic pictures

Get uploaded picture

Setting image Information

The database handles dynamic mapping

Get dynamic illustrations

Finally, we also need to modify the dynamic query, because users have avatars, they need to query together

The project address, interested partners can go to download, have a try oh

Koa interface, end of project

Previous articleFinished the comment, reply to the comment, query the comment, delete the comment

Create label tables for multi-table queries

Finish creating the label

Process the data

Add to database

test

Assign labels to the dynamic

Check whether the label passed by the user exists

The label and dynamic ID are obtained and passed to the database processing file

Get the data, process it

Upload the picture

implementationavatarHandlerThe middlewarenpm install koa-multer

Get the information about the uploaded image and save it to the database

Access to image

Setting image Information

The query image

Upload dynamic pictures

Get uploaded picture

Setting image Information

The database handles dynamic mapping

Get dynamic illustrations

Finally, we also need to modify the dynamic query, because users have avatars, they need to query together

The project address, interested partners can go to download, have a try oh

Related Posts

Debug the Front-end WebView guide

Discussion on IoC Container

Js to achieve the tower of magic games

implementation`avatarHandler`The middleware`npm install koa-multer`