NodeJS/MySQL: Use Results of Previous SELECT Query?-CodePudding

Building a simple ToDo app in React/NodeJS/Express with MySQL. Users join a group ("family" in the code), then tasks are viewable filtered by familyId. To create a task, I first have a query that finds the user's familyId from the Users table, then I want to include that familyId value in the subsequent INSERT query for creating the task row in the Tasks table. My task.model.js is below:

const sql = require("./db.js");

// constructor
const Task = function(task) {
    this.title = task.title;
    this.familyId = task.familyId;
    this.description = task.description;
    this.completed = task.completed;
    this.startDate = task.startDate;
    this.userId = task.userId;
};

Task.create = (task, result) => {
    sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, res) => {
        if (err) {
            console.log("Error selecting from USERS: ", err);
            return result(err, null);
        }
        sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [result, task.title, task.description, task.completed, task.startDate], (err, res) => {
            if (err) {
                console.log("Error inserting in TASKS: ", err);
                return result(err, null);
            }
        })
        console.log("created task: ", { id: res.insertId, ...task });
        return result(null, { id: res.insertId, ...task });
    });
};

However, I cannot figure out how to properly use the familyId result of the SELECT query as a parameter in the suqsequent INSERT query. I know the overall syntax works because I can manually plug in an ID value as a parameter and the entire operation completes successfully - I just need to know how to use the resule of the first query in the next.

CodePudding user response：

The way you are using it should work but the problem is you have defined the callback as res but are passing result in the 2nd sql query

sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, res) => {
    if (err) {
        console.log("Error selecting from USERS: ", err);
        return result(err, null);
    }
    //res should have the value for the familyId of the given user so in next line pass res not result
    sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [res[0].familyId, task.title, task.description, task.completed, task.startDate], (err, res) => {
        if (err) {
            console.log("Error inserting in TASKS: ", err);
            return result(err, null);
        }
    })
    console.log("created task: ", { id: res.insertId, ...task });
    return result(null, { id: res.insertId, ...task });
});

CodePudding user response：

SQL returns array in result , so use result[0] to get first Object , and then access the object key by result[0].keyName

Task.create = (task, result) => {
        sql.query("SELECT familyId FROM users WHERE userId = ?", task.userId, (err, users) => {
            if (err) {
                console.log("Error selecting from USERS: ", err);
                return result(err, null);
            }

            let familyId = users && users[0] ? users[0].familyId : null;

            sql.query("INSERT INTO tasks (familyId, title, description, completed, startDate) VALUES (?,?,?,?,?)", [familyId, task.title, task.description, task.completed, task.startDate], (err, res) => {
                if (err) {
                    console.log("Error inserting in TASKS: ", err);
                    return result(err, null);
                }
            })
            console.log("created task: ", { id: res.insertId, ...task });
            return result(null, { id: res.insertId, ...task });
        });
    };