Wait for mysql to finish queries in a for loop before finishing a function in nodeJS-CodePudding

I am trying to run a query to get some data from a table, then use that array of data to get some data from another table to then return it as JSON.

I have been trying for a while but I cannot seem to figure out async and await. Right now it does sort of work but doesn't wait for my second query in the for loop to finish before returning data.

app.get("/get-top-trending", (request, response) => {
    const req = request.query
    let query = 'SELECT Ticker, Mentions FROM trend_data ORDER BY Date DESC, '   req.by   ' DESC LIMIT 3';
    let returnData = {};

    cryptoDB.query(query, (err, tickers) => {
        if (err) throw err;

        getData(tickers).then(function() {
            response.send(returnData)
        });
    });

    async function getData(tickers) {
        for (let i = 0; i < tickers.length; i  ) {
            cryptoDB.query('SELECT HistoricalJSON FROM historical_trend_data WHERE Ticker=? LIMIT 1', [tickers[i]['Ticker']], (err, rows2) => {
                if (err) throw err;
                returnData[tickers[i]['Ticker']] = rows2[0]['HistoricalJSON'];
            });
        }
    }
});

I assume that something has to be done in the getData async function, however I am not particularly sure how to implement a working solution. I have tried promises but they don't seem to work the way that I expect.

Any guidance would be appreciated.

CodePudding user response：

first solution:

app.get("/get-top-trending", (request, response) => {
const req = request.query
let query = 'SELECT Ticker, Mentions FROM trend_data ORDER BY Date DESC, '   req.by   ' DESC LIMIT 3';

cryptoDB.query(query, (err, tickers) => {
    if (err) throw err;

    getData(tickers).then(function (returnData) {
        response.send(returnData)
    });
});

async function getData(tickers) {
    const returnData = {};

    const querys = ((ticker) => {
        return new Promise((resolve, reject) => {
            cryptoDB.query('SELECT HistoricalJSON FROM historical_trend_data WHERE Ticker=? LIMIT 1', [ticker['Ticker']], (err, rows2) => {
                if (err) reject(err);
                returnData[ticker['Ticker']] = rows2[0]['HistoricalJSON'];
                resolve();
            });
        })
    })

    for (let i = 0; i < tickers.length; i  ) {
        await querys(tickers[i]);
    }

    return returnData
}

});

second solution:

app.get("/get-top-trending", (request, response) => {
    const req = request.query
    let query = 'SELECT Ticker, Mentions FROM trend_data ORDER BY Date DESC, '   req.by   ' DESC LIMIT 3';


    cryptoDB.query(query, (err, tickers) => {
        if (err) throw err;

        getData(tickers).then(function(returnData) {
            response.send(returnData)
        }).catch(error => throw error);
    });

    async function getData(tickers) {
        let returnData = {};
        for (let i = 0; i < tickers.length; i  ) {
            returnData[tickers[i]['Ticker']] = await getTickerQuery([tickers[i]['Ticker']]);
        }
        return returnData;
    }
    
    function getTickerQuery(ticker) {
        return new Promise((resolve, reject) => {
            cryptoDB.query('SELECT HistoricalJSON FROM historical_trend_data WHERE Ticker=? LIMIT 1', ticker, (err, rows2) => {
                if (err) throw reject(err);
                resolve(rows2[0]['HistoricalJSON']);
            });
        })
    }
});

I recommend second solution for readability