具有Express&的Node.js APImysql-获取记录数，页码，...&提供分页 [英] Node.js API with express & mysql - Getting record count, page number, ... & provide pagination

问题描述

我想在响应中获取并提供total_record_count，page_number，page_size，total_pages，has_more信息，并能够对结果进行分页，因为我将查询限制为100.最佳方法是什么?

I would like to get and provide the total total_record_count, page_number, page_size, total_pages, has_more information in the response and being able to paginate through the result since I LIMIT the query to 100. What's the best way to do this?

这是我当前的设置:

router.get('/?', function(req, res, next) {
    const sql = "SELECT * from users ";
    const existingParams = ["title", "active"].filter(field => req.query[field]);

    if (existingParams.length) {
        sql += " WHERE ";
        sql += existingParams.map(field => `${field} = ?`).join(" AND ");
        sql += " LIMIT 100 ";
    }

    connection.query(
        sql,
        existingParams.map(field => req.query[field]),
        function (error, results, fields) {
            res.json({"status": 200, "error": null, "total_record_count": 604, "page_number": 1, "page_size": 100, "total_pages": 7, "has_more": true, "records": results});
        }
    );
});

在URL中，如果total_record_count超过LIMIT，我希望能够提供/允许页面参数'p'.因此，查询参数p从1开始指定要返回的页面.如果省略p参数，则默认值为1.

In the url I would like to be able to provide/allow a page parameter 'p' if the the total_record_count exceeds the LIMIT. So the query parameter p specifies which page to return, starting with 1. If the p parameter is omitted, the default value is 1. Sth like:

http://localhost:4001/api/v1/users/?active=0&title=mr&p=1

推荐答案

对于分页，您需要在MySQL中进行以下查询

For pagination, you need a query something like below in MySQL

SELECT * FROM users LIMIT 0,10

有两个参数，第一个参数指定要返回的第一行的偏移量，第二个参数指定要返回的最大行数.第一行的偏移量为0(而不是1).

With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1).

由于您希望第一页的默认值和100个结果

As you want to have default value as 1st page and 100 result

router.get('/?', function(req, res, next) {
    const sql = "SELECT * from users ";
    const existingParams = ["title", "active"].filter(field => req.query[field]);
    const pageNum = req.query.p || 1;
    const pageSize = req.query.p_size || 100;


    if (existingParams.length) {
        sql += " WHERE ";
        sql += existingParams.map(field => `${field} = ?`).join(" AND ");
    }
    sql += ` LIMIT  ${(pageNum - 1) * PageSize},${PageSize}`;
    ...
});

，对于第二个有关提供总行数的问题，您需要为此运行两个查询.您可以在mysql中利用 SQL_CALC_FOUND_ROWS和FOUND_ROWS()> 4.0.但是，当我们在查询中为WHERE/ORDER子句提供适当的索引时，使用两个单独的查询而不是使用SQL_CALC_FOUND_ROWS进行查询要快得多(一个用于数据，另一个用于获取所有行数).

and for your the second question about providing total row count, you need to run two queries for that. You can utilize SQL_CALC_FOUND_ROWS and FOUND_ROWS() in mysql>4.0. But when we have appropriate indexes for WHERE/ORDER clause in our query, it is much faster to use two separate queries instead of one with SQL_CALC_FOUND_ROWS.(one for data and one for getting all row count).

现在您必须并行运行两个查询以提高性能，因此通过回调您可以利用此函数已经编写了用于并行运行回调的代码，并等待所有回调完成.或者，您可以在此处使用承诺的MySQL库，也可以使用 Bluebird 使当前的库变为可用.a>.

Now you have to run both the queries parallel for performance so with callback you can utilize this function that I have written for running callback in parallel and wait until all callbacks are completed. Or you can use promisified MySQL library here or you can make your current library promisified using Bluebird.

赞:

const connection = mysql.createConnection({.....});
global.db  = Bluebird.promisifyAll(connection);
db.queryAsync("SELECT * FROM users")
.then(function(rows){ 
    console.log(rows);
})

然后像这样运行查询

Promise.all([
    db.queryAsync("SELECT * FROM users WHERE title='ridham' LIMIT 0,10"), // generated by your code
    db.queryAsync("SELECT COUNT(*) FROM users WHERE title='ridham'")
]).then(([data, count]) => {
        // your logic to send response
})

或者您也可以运行以下查询，这也可以

Or you can also run following query, that would also work

SELECT * FROM'table'JOIN(SELECT COUNT(*)FROM'table')t2 WHERE title ='ridham'LIMIT 0,10)

此外，您也可以使用ORM，例如沉寂或

Also, you can use ORM like Sequilize or Waterline. That would make your like 10x easier for MYSQL at least.

例如，在sequilize中:

as example in sequilize:

User.findAndCountAll({
    where: {
        title: {
          [Op.like]: 'ridham'
        }
    },
    offset: 10,
    limit: 2
})
.then(result => {
    console.log(result.count);
    console.log(result.rows);
});

这篇关于具有Express&的Node.js APImysql-获取记录数，页码，...&提供分页的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！