javascript - 获取每个位置的 10 条最新评论

Question

我正在编写一个查询，使用 Node 和 Express 中的池。我正在尝试做的是，获取每个位置的 10 条最新评论。我正在尝试使用以下代码执行此操作。

var returnObj = [];

router.get('/', (req, res) =>{
    const LocationsQuery = 'SELECT locationID FROM locations GROUP BY locationID';

    connection.getConnection(function(err, connection) {
        connection.beginTransaction(function(err) {
            if (err) {                  
                connection.rollback(function() {
                    connection.release();
                });
            } else {
                connection.query(LocationsQuery, function(err, results) {
                    if (err) {      
                        connection.rollback(function() {
                            connection.release();
                        });
                    }   

                    for(var i = 0; i < results.length;i++){
                        var dataQuery = `SELECT  locations.businessName, locations.address, locations.city, locations.state, locations.zipCode, locations.Country, r.comfort,
                                            r.timeliness, r.politeness, r.accessibility, r.averageRating, r.food, r.breakRoom, r.restroom
                                            FROM (SELECT reviews.locationID, reviews.comfort,  reviews.timeliness, reviews.politeness,  reviews.accessibility,
                                                    (reviews.comfort +  reviews.timeliness + reviews.politeness +  reviews.accessibility) / 4 AS averageRating, 
                                                    reviews.food, reviews.breakRoom, reviews.restroom
                                                    FROM reviews 
                                                    WHERE reviews.locationID = ${results[i].locationID}) r 
                                        LEFT JOIN locations on locations.locationID = r.locationID   
                                        LIMIT ${config.limit}`

                        connection.query(dataQuery,function(err, res) {

                            if (err) {      
                                connection.rollback(function() {
                                    connection.release();
                                });
                            } 

                            //Get the Averages of the ratings then creates 
                            // Json Object out of the MYSQL return Data 
                            object = setup(res)
                            returnObj.append(object)
                            console.log(object)
                        });
                    } //End For loop
                });
            }    
        });
    });
    res.send(returnObj);
});

我遇到的问题是对数据库进行 n+1 查询。（获取所有位置的一个，n 循环遍历这些位置并获得 10 个）

理论上，这段代码确实做了我想要的，因为它确实“获取”了每个位置的 10 条最新评论。我遇到的问题是，我不能仅仅res.send()因为它在每个查询完成之前发送。我试图附加到returnObj然后发送它，就像上面一样，但这似乎不起作用。

谁能帮我弄清楚如何在单个查询中获取数据，或者如何让这个等到所有最近评论的位置都被查询？

标签： javascriptmysqlnode.jsexpress

Answer 1

数据库操作在 nodejs 中是异步的，因此您需要处理异步行为，并且您不能在 for 循环中调用 mysql 查询。所以这是你的最终解决方案

    import { resolve } from "path";
import { rejects } from "assert";

var returnObj = [];

router.get('/', (req, res) => {
    const LocationsQuery = 'SELECT locationID FROM locations GROUP BY locationID';

    connection.getConnection(function (err, connection) {
        connection.beginTransaction(function (err) {
            if (err) {
                connection.rollback(function () {
                    connection.release();
                });
            } else {
                connection.query(LocationsQuery, function (err, results) {
                    if (err) {
                        connection.rollback(function () {
                            connection.release();
                        });
                    }
                    var promises = [];
                    for (var i = 0; i < results.length; i++) {
                        promises.push(performopration, results[i], config);
                    } //End For loop

                    Promise.all(promises)
                    .then(() => {
                        res.send(returnObj);
                    })
                });
            }
        });
    });
});

function performopration(connection, results, config) {
    var dataQuery = `SELECT  locations.businessName, locations.address, locations.city, locations.state, locations.zipCode, locations.Country, r.comfort,
                                            r.timeliness, r.politeness, r.accessibility, r.averageRating, r.food, r.breakRoom, r.restroom
                                            FROM (SELECT reviews.locationID, reviews.comfort,  reviews.timeliness, reviews.politeness,  reviews.accessibility,
                                                    (reviews.comfort +  reviews.timeliness + reviews.politeness +  reviews.accessibility) / 4 AS averageRating, 
                                                    reviews.food, reviews.breakRoom, reviews.restroom
                                                    FROM reviews 
                                                    WHERE reviews.locationID = ${results.locationID}) r 
                                        LEFT JOIN locations on locations.locationID = r.locationID   
                                        LIMIT ${config.limit}`;

    return new Promise((resolve, rejects) => {
        connection.query(dataQuery, function (err, res) {

            if (err) {
                connection.rollback(function () {
                    connection.release();
                });
                rejects();
            }

            //Get the Averages of the ratings then creates 
            // Json Object out of the MYSQL return Data 
            object = setup(res)
            returnObj.append(object);
            console.log(object);
            resolve();
        })
    })


}