[nodejs advanced journey (2)]: use koa2+mysql to realize list data paging

1. Display effect (pagination)

2. Paging main fields

The main fields of paging include pageSize number of items per page, pageNum number of pages, startRow current starting page number, endRow current ending page number, and total total number. Mainly according to the parameters of front-end paging, after processing, returning the correct data of the front-end is actually a very common and simple function. But it is very and very important.

pageSize 每页条数
pageNum  第几页
startRow 当前开始页编号
endRow 当前结束页编号
total 总数量

Calculation 3.startRow and endRow

encapsulated as a function

/* 分页公共函数*/
module.exports = handlePages = (pageNum, pageSize, total) => {
    let startRow = total > 0 ? ((pageNum - 1) * pageSize + 1) : 0;
    let endRow = pageNum * pageSize <= total ? pageNum * pageSize : total;
    return {
        pageNum,
        pageSize,
        recordCounts:total,
        startRow,
        endRow
    }
}

4. Use this function in the interface function

Obtained from the front-end parameters, the data required for paging.

 let { nickname, name, role, pageSize, pageNum } = ctx.request.body;
 
 //此处进行处理
 let pageNum1 = (pageNum - 1) * pageSize

//获取用户信息列表
async getAllUserList(ctx) {
   let { nickname, name, role, pageSize, pageNum } = ctx.request.body
   let res = []
   let pageNum1 = (pageNum - 1) * pageSize
   let total = (await User.getAllUserListTotal())[0].recordCounts
   if (!nickname && !name && !role) {
     res = (await User.getAllUserListNotCond(pageSize, pageNum1))
   } else {
     res = (await User.getAllUserList(nickname, name, role, pageSize, pageNum1))
   }
   ctx.body = {
     code: 0,
     data: res.map(v => {
       if (v.password) {
         delete v.password
       }
       return v
     }),
     //分页所有的参数
     ...handlePages(pageNum, pageSize, total)
   }
 }

5. MySql statement in paging

Query database data based on pagination

//根据分页查询用户列表
async getAllUserListNotCond(pageSize, pageNum) {
        return await query(`SELECT * FROM user LIMIT ${pageNum},${pageSize}`)
}

mySql gets the total number of database data

//获取用户信息列表的总条数
async getAllUserListTotal() {
   return await query(`SELECT COUNT(*) as recordCounts FROM user`)
}

About mySql's COUNT() function

Role: The COUNT() function returns the number of rows that match the specified condition.

SQL COUNT(column_name) syntax

The COUNT(column_name) function returns the number of values in the specified column (NULL is not counted):

SELECT COUNT(column_name) FROM table_name

SQL COUNT(*) syntax

The COUNT(*) function returns the number of records in the table:

SELECT COUNT(*) FROM table_name

SQL COUNT(DISTINCT column_name) syntax

The COUNT(DISTINCT column_name) function returns the number of distinct values for the specified column:

SELECT COUNT(DISTINCT column_name) FROM table_name

6. Switch the effect of paging

The effect after switching pagination is still normal.

7. Summary

Mainly includes the basic use of mysql. Currently still learning, there are inappropriate places, welcome to correct and exchange learning. Currently working on the rights management module. The next article will explain how to control permissions, and those who are interested can continue to pay attention.