Getting the 10 most recent reviews for each location

Question

I am Writing a Query, using pools in Node and Express. What I am attempting to do it, get the 10 most recent reviews for each location. I am attempting to do this using the following code.

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);
});

The issue I am running into is this doing n+1 queries to the database. (The one to get all the locations, and n to loop through those and get 10)

In theory this code does do exactly what I want as it does "get" the 10 most recent reviews for each location. The issue I am running into is the fact that I cannot just res.send() due to it sending before every query is complete. I have tried to append to the returnObj and then send that, like above, but that doesn't seem to work.

Can anyone help me figure out how to get the data in either a single query or how I can make this wait until all of the locations most recent reviews are queried?

Create promises for each query and use `Promise.all()` to wait for all of them. — Barmar, Nov 07 '19 at 02:48
Or see https://stackoverflow.com/questions/2129693/mysql-using-limit-within-group-by-to-get-n-results-per-group for how to do a single query that gets N results per group, instead of the loop. — Barmar, Nov 07 '19 at 02:48

score 1 · Accepted Answer · answered Nov 07 '19 at 07:03

Database opration is async in nodejs so you need to handle async behaviour and you can not call mysql query inside for loop. so here is your final solution

    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();
        })
    })


}

Getting the 10 most recent reviews for each location

1 Answers1