Nested SQL queries in function in node.js

Question

First of all, i'm new in JS. I have a function that possibly can use multiple requests to get the final data. How can i do this in the right way? In this example participants won't pushed to the dialogs array because it's in async call.

function getDialogs(token, callback) {
    //getting user id
    con.query("SELECT user_id FROM users_tokens WHERE user_token = '" + token + "'", function(error, results) {
        if (error) {
            throw error;
        }
        var userId = results[0].user_id;
        //getting all conversation
        con.query("SELECT cc.id as conversation_id, cc.type FROM chat_conversations cc INNER JOIN chat_participants cp ON cc.id = cp.conversation_id WHERE cp.user_id = " + userId + " GROUP BY cc.id", function (error, results) {
            if (error) {
                throw error;
            }
            var dialogs = [];
            for (let i = 0; i < results.length; i++) {
                var dialog = {id: results[i].conversation_id};
                //getting chat participants
                con.query("SELECT user_id FROM chat_participants WHERE conversation_id = " + results[i].conversation_id + " AND user_id != " + userId, function (error, results) {
                    var participants = [];
                    for (let j = 0; j< results.length; j++) {
                        participants.push(results[j].user_id);
                    }
                    dialogs[participants] = participants;
                });
                dialogs.push(dialog);
            }
            callback(dialogs);
        });
    });
}

Don't use string concatenation, use `?` placeholders in the queries. — Barmar, Aug 12 '22 at 15:39
You don't need multiple queries. You can do all these queries in one with JOIN. — Barmar, Aug 12 '22 at 15:40
Use the promise version of node-mysql. Then you can call `await` in the `for` loop to get all the results in an array. — Barmar, Aug 12 '22 at 15:42
In this example, probably yeah, i can do it in one query. But i was wondering in general, how can i do it if i have to use multiple queries. Thanks i'll take a look at node-mysql — Bobby Redjeans, Aug 12 '22 at 15:49
See https://stackoverflow.com/questions/14220321/how-to-return-the-response-from-an-asynchronous-call — Barmar, Aug 12 '22 at 15:50
Doing multiple queries should be avoided strenuously. It's a practice that has a name, the "N+1 problem". All those round trips to the database server add up to a lot of latency. With techniques like Computed Table Expressions and judicious use of `UNION ALL`, it is almost always possible to create queries that avoid those repeated network trips. — Pointy, Aug 12 '22 at 15:52

score 1 · Answer 1 · answered Aug 12 '22 at 15:54

Technically you can use a single request like this

SELECT user_id FROM chat_participants WHERE conversation_id IN (
   SELECT
      cc.id as conversation_id,
      cc.type
    FROM
      chat_conversations cc
      INNER JOIN chat_participants cp ON cc.id = cp.conversation_id
    WHERE
      cp.user_id IN (
        SELECT
          user_id
        FROM
          users_tokens
        WHERE
          user_token = "TOKEN"
      )
    GROUP BY
      cc.id
  )

but there are a few problems with this approach as well.
First of all, it seems like you are only using the user_id of your first row, so please use LIMIT 1 in such cases.
Second of all, it seems like user_id won't ever have a duplicate, so make it a primary key.
Third of all, don't concat your token, node mysql supports having placeholders using ? in your query, like this:

con.query("SELECT * FROM ? WHERE user_id = ?", ["table_name", "userid"])

Fourth of all, promisify your requests so you don't have a callback hell, do something like:

function promiseRequest(query, placeholders) {
   return new Promise((res, rej) => {
      con.query(query, placeholders, (err, data) => {
        if (err) rej(err);
        res(data);
      });
    });
}

Thanks for the answer! Btw, i'm not sure that i can use a query like this since each conversation can have multiple participants (group chat) — Bobby Redjeans, Aug 12 '22 at 19:25

Nested SQL queries in function in node.js

1 Answers1