Use async function to retrieve data while looping through from another function to insert objects

Member_15892067

Hello! I want to say that I just started working with nodejs. I used before php/laravel. I have 2 tables, one is for posts one is for posts_actions. Every user can create a post and other users can like/comment on that post. Every action on that post (eg: like) will be stored in table posts_actions. I'm trying to load the posts from database, loop through them to add an extra object with all the data from table posts_actions. But at the end, when I send back the data to frontend, the data is empty, why is that and how can I solve this?

app.post("/api/loadposts", function(req, res) {
let posts = [];
let posts_actions = [];
const userid = req.body.userID;

db.query("SELECT \* FROM posts ORDER BY id DESC",
(error, result) => {
    if(error) {
        return res.status(200).send({ error: true });
    }
   
    posts = result;
});

for(let p; p < posts.length; p++)
{
    db.query("SELECT \* FROM posts\_actions WHERE post\_id = ? AND user\_id = ? LIMIT 1", \[posts\[p\].id, userid\],
        (e, r) => {
            if(e) {
                posts\[p\].action = \[\];
            }
            else if(r.length > 0) {
                posts\[p\].action = r\[0\];
            }
            else {
                posts\[p\].action = \[\];
            }
    });
}

res.status(200).send({ data: posts });

});

I know that I must use async/await/promise, I followed some topics and tried the code below, but the data I send to frontend is empty. A little help here, what can I do to have the result I want?

async function doQuery(query, params = []) {
function queryData(query, params) {
return new Promise(function(resolve, reject) {
db.query(query, params, function (err, rows, fields) {
if (err) {
return reject(err);
}
resolve(rows);
});
});
}

queryData(query, params).then(function(v) {
    return v;
}).catch(function(v) {
    return \[\];
})

return null;

}

async function getAllPosts(userid)
{
try {
let posts = await doQuery("SELECT * FROM posts ORDER BY id DESC");

    for(let p = 0; p < posts.length; p++)
    {
        let actions = await doQuery("SELECT \* FROM posts\_actions WHERE post\_id = ? AND user\_id = ? LIMIT 1", \[posts\[p\].id

Richard Deeming

Your doQuery function always returns null. You need to start by fixing that:

With that change in place, your other code should work - although you'll probably want to catch any errors thrown when you load the actions for an individual post, rather than throwing all of the posts away. If your database supports it, you may also want to load the post actions in parallel, rather than serially:

async function loadPostActions(post, userid) {
try {
post.actions = await doQuery("SELECT * FROM posts_actions WHERE post_id = ? AND user_id = ? LIMIT 1", [post.id, userid]);
} catch (error) {
post.actionsError = error;
}
}

async function getAllPosts(userid)
{
try {
const posts = await doQuery("SELECT * FROM posts ORDER BY id DESC");

    const actionPromises = \[\];
    for (let p = 0; p < posts.length; p++) {
        actionPromises.push(loadPostActions(posts\[p\], userid));
    }
    
    await Promise.all(actionPromises);
    return posts;
} catch (error) {
    return error;
}

}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member_15892067

Thank you very very much! That works perfectly! But apparently, on postman the data I receive is good, but on frontend the action object inside data is empty and it shouldn't be. This is what I get with postman:

{
"data": [
{
"id": 2,
"user_id": 1,
"text": "d2222222",
"posted_at": "1/12/2023 1:52:27 PM",
"likes": 1,
"comments": 0,
"picture": "",
"userid": 1,
"name": "Gilbert",
"actions": [
{
"id": 1,
"tweet_id": 2,
"user_id": 1,
"liked": 1,
"retweeted": 0,
"replied_count": 0
}
]
}
]
}

And on frontend (Reactjs):

{
"data": [
{
"id": 2,
"user_id": 1,
"text": "d2222222",
"posted_at": "1/12/2023 1:52:27 PM",
"likes": 1,
"comments": 0,
"picture": "",
"userid": 1,
"name": "Gilbert",
"actions": []
}
]
}

useEffect(() => {
loadAllPosts();
}, [])

function loadAllPosts() {
    Axios.post('/api/loadposts', {
        userID: loginData.id
    }).then((response) => {
        console.log(response.data.data)
        setPosts(response.data.data);
    }).catch((error) => {
        console.log("Something went wrong.. We are investigating.");
    })
}

Richard Deeming

That's odd; if it works for one, it should work for the other. You could try loading the actions serially, in case Node has a problem with Promise.all:

async function getAllPosts(userid)
{
try {
const posts = await doQuery("SELECT * FROM posts ORDER BY id DESC");

    /\*
    const actionPromises = \[\];
    for (let p = 0; p < posts.length; p++) {
        actionPromises.push(loadPostActions(posts\[p\], userid));
    }
    
    await Promise.all(actionPromises);
    \*/
    
    for (let p = 0; p < posts.length; p++) {
        await loadPostActions(posts\[p\], userid);
    }
    
    return posts;
} catch (error) {
    return error;
}

}

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Member_15892067

I solved this. I needed to put an if statment on useEffect if the user is logged (because I couldn't get his userID in the beginning to send a post request).

useEffect(() => {
if(loginStatus === true) {
loadAllPosts();
}
}, [loginStatus])

Thank you very very much @RichardDeeming for helping me out! Btw, loading parallel in my "context" lets says, is more efficient (memory, loading time, etc) instead of serially? Or is just because using serially will throw all posts if a promise is rejected (fail)?

Richard Deeming

Member 15892067 wrote:

Btw, loading parallel in my "context" lets says, is more efficient (memory, loading time, etc) instead of serially? Or is just because using serially will throw all posts if a promise is rejected (fail)?

Loading in parallel should be faster, if the database supports it. You're telling it to start loading the actions for all posts, then waiting for them all to finish, rather than waiting for it to load the actions for the first post, then waiting for it to load the actions for the second post, ... For example, assume it took an average of 50ms to load the actions for one post, and you want to load the actions for 10 posts. Doing that serially would take around 500ms. Doing it in parallel should take around 50ms. This article is about C#, but many of the same principals apply: Making an Asynchronous Breakfast in .NET[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Jeremy Falcon

Couple things to consider here. First and foremost, do not use successive round trips in an API call as your first solution. It's always a last resort. It's much quicker to doing a JOIN on you indexes to combine the data. Yes this first payload be larger, but you only need to establish the connection once and you're still using the same sized payload even if it's split up into calls. So, the proper solution is to use a query or view and return that info to the client (the client being the Express server in this instance) in one trip... you can paginate if you need to break up the payload size into several calls. To answer your question, and I do not suggest you do that this at all, but to address it... In your doQuery function, notice that only the reject case in returned in your closure. Not the cause of your issue, but to be consistent. Also, the function doesn't need to return an explicit promise. It's being done already under the hood. Use try/catch in your async functions to handle error conditions. The syntax is much cleaner.

async function doQuery(query, params = []) {
try {
return await new Promise((resolve, reject) => {
db.query(query, params, (err, rows) => {
if (err)
reject(err);
else
resolve(rows);
});
});
} catch {
return [];
}
}

If your data is still null after reworking that routine than make sure db.query is returning valid data. Also, if you plan on making successive calls like this, you'd be much, much better off using [generators](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global\_Objects/Generator) . But, that is a last choice. You should be redoing your DB design and queries first.

Jeremy Falcon

Leon Scott Kennedy

Now you are using SQL database with nodejs and created 2 tables to store post data. Sometimes, SQL DB is more effective than no-SQL DBs like mongoDB but in your case, the best optimized method is using MongoDB. By its property, mongoDB is the fittest one for your current project. If you need to use only SQL DB, you can consider about ORM in SQL DB. Wish your good.