前端之家

  1. 首页
  2. 问答

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

• 问答

我正在使用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,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上，然后像上面一样发送，但这似乎不起作用。

任何人都可以帮助我弄清楚如何通过单个查询获取数据，或者如何才能等到查询所有最近评论的地点时才进行查询？

aiaiai5 回答：获取每个位置的10条最新评论

数据库操作在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,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();
        })
    })


}
expressjavascriptmysqlnode.js
本文链接：https://www.f2er.com/3147865.html

大家都在问