How to update the variable from the result of connection.query function?

Question

I am creating a simple web application and am stuck on a simple problem. I want to update the temp variable from the results of my inner query. I know that connection.query might take some time, so the rest of the code is executed first. In the final response, I am not getting the value of temp.user_image and temp.user_name.

router.get("/home/getArticles", (req, res) => {
    var select_query = "SELECT * FROM `articles` WHERE 1";
    connection.query(select_query, async (err, result) => {
        if (err) {
            return res.status(500).json({ message: "Error while processing your request" });
        }
        var final_data = [];
        for (var i = 0; i < result.length; i++) {
            var temp = {};
            let current_user_id = result[i].user_id;
            var select_query = "SELECT * FROM `users` WHERE `user_id`=" + mysql.escape(current_user_id) + "";
            connection.query(select_query, (err, innerResult) => {
                if (err) throw err;
                temp.user_name = innerResult[0].user_name;
                temp.user_image = innerResult[0].user_profile_image;
            });
            temp.title = result[i].title;
            temp.body = result[i].detail;
            final_data.push(temp);
        }
        return res.status(200).json({ data: final_data });
    })
})

score 1 · Accepted Answer · answered Feb 13 '23 at 06:42

There are 2 problems in your code:

The server response before the second query finish - which you've already known.
This way of querying relationship will result in extremely bad performance known as N+1 query problem

For an example, we can use SQL JOIN clause for querying relationship:

router.get("/home/getArticles", (req, res) => {
    var select_query = "SELECT articles.*, users.user_name, users.user_profile_image AS user_image FROM `articles` LEFT JOIN `users` ON `articles`.`user_id` = `users`.`id` WHERE 1";
    connection.query(select_query, async (err, result) => {
        if (err) {
            return res.status(500).json({ message: "Error while processing your request" });
        }
        return res.status(200).json({ data: result });
    })
})

How to update the variable from the result of connection.query function?

1 Answers1