Create a new array from the result of two MySQL SELECT queries that run in sequence (2nd query runs-CodePudding

I am struggling with an array issue in my Nodejs and MySQL backend (React frontend). The frontend sends a request with two objects it’s body, the API then performs a SELECT query against table 1 using the two objects as criteria. The result of this query is an array of objects like this:

[
    {
        "userID": 245
    },
    {
        "userID": 244
    },
    {
        "userID": 247
    }
]

I then perform a second SELECT query against table 2 with the userIDs from the first query as the criteria (each userID contains “firstName” and “lastName” properties in table 2). What I am trying to achieve is like this below, which I can send back to the frontend:

[
  {
    userID: 245,
    firstName: "Test",
    lastName: "Student1",
  },
  {
    userID: 244,
    firstName: "Test",
    lastName: "Student2",
  },
  {
    userID: 247,
    firstName: "Test",
    lastName: "Student3",
  },
];

Here is the function I have so to achieve this:

export const view = async (req, res) => {
  const { classIdentifier, syllabusCode } = req.body;
  // User the connection

  db.query(
    "SELECT DISTINCT studentID AS 'userID' FROM classIndex WHERE teacherID = ? AND classIdentifier = ? AND syllabusCode = ?;",
    [req.params.teacherID, classIdentifier, syllabusCode],
    (err, rows) => {
      const allData = [];
      const final = async () => {
        await rows.forEach((row) => {
          db.query(
            "SELECT userID, firstName, lastName from userIndex WHERE userID = ?",
            [row.userID],
            (err, data) => {
              if (!err) {
                data.forEach((users) => {
                  allData.push({
                    userID: users.userID,
                    firsName: users.firstName,
                    lastName: users.lastName,
                  });
                });
              } else {
                console.log(err);
              }
            }
          );
        });
      };
      final().then((result) => {
        console.log(result);
      });
      console.log(allData);
    }
  );
};

Please let me know if I can supply any more code, any assistance will be greatly appreciated!

CodePudding user response：

You could use a nested Query. Right now u run

SELECT DISTINCT studentID AS 'userID' FROM classIndex WHERE teacherID = ? AND 
classIdentifier = ? AND syllabusCode = ?;

and then the 2nd query for every entry.

You could combine the queries and let mysql do the job for you. (I'm just not sure if the Distinct has to be there at that point inside the nested query.)

SELECT firstName, lastName, userID FROM userIndex WHERE userID LIKE (
   SELECT DISTINCT studentID
   FROM classIndex 
   WHERE teacherID = ? AND classIdentifier = ? AND syllabusCode = ?
)

Short Info to Subsqueries