How to add a second level query to axios query?

Question

I am querying an objects table in axios like this:

app.get(`/details`, (req, res) => {
  const { id } = req.query;
  connection.query(
    `SELECT
      objects.*
    FROM objects
    WHERE id = ${id}
    LIMIT 1`,
    (err, results) => {
      if (err) {
        return res.send(err);
      } else {
        let result = results[0];
        result.relationships = [];
        result.tasks = [];
        return res.json(result);
      }
    }
  );
});

How can I add the following two queries to my result results.relationship:

`SELECT objects.* FROM objects WHERE objects.relationship = ${result.id}

and to my result results.tasks:

`SELECT tasks.* FROM tasks WHERE tasks.relationship = ${result.id}

Correct me if i am wrong , but what does this have to do with axios ? — Kevin.a, Nov 04 '20 at 10:25
You can just keep going into the next callback; outside variables will be accessible at any time that way. Or you switch to Promises and use `await`, a much better solution. — , Nov 04 '20 at 10:25
When it has nothing to do with axios, correct me and I'll update the tags. — T. Karter, Nov 04 '20 at 10:26
Looks like, you have to promisify it first when you are creating connection object. follow this link then you will be able to use .then() as promise or await. https://www.codota.com/code/javascript/functions/mysql/Connection/query — Sachin Vishwakarma, Nov 04 '20 at 10:26
@ChrisG Thank you. Can you put that into an answer? I don't know how to transfer that hint to code. Would be very helpful. — T. Karter, Nov 04 '20 at 10:27
I assume this is node, express and mysql? https://stackoverflow.com/questions/44004418/node-js-async-await-using-with-mysql or use `mysql2` instead: https://www.npmjs.com/package/mysql2#using-promise-wrapper — , Nov 04 '20 at 10:28
Using "callback hell" instead is just about keeping on doing what you're already doing. Instead of `res.json(result);`, simply run another connection.query(...) and then in its callback, run another query, and in its callback, put `res.json(result)`. — , Nov 04 '20 at 10:31
It’s a good idea to join both the table and extract out the result in one query — Aamin Khan, Nov 04 '20 at 10:31
@ChrisG Looks like a mess, but I tried to cover it in a new answer. Anyhow it is working — T. Karter, Nov 04 '20 at 10:42
Duplicate: [node.js async/await using with MySQL](https://stackoverflow.com/questions/44004418/node-js-async-await-using-with-mysql) — , Nov 04 '20 at 10:49
@ChrisG I read the answers from your link but I don't see how they answer my question. — T. Karter, Nov 04 '20 at 10:59
@ChrisG I think this would be exactly what you want me to do: https://pastebin.com/1jqwjQU6 It's working with the only downside, I am getting only one row for tasks are relatedObects. — T. Karter, Nov 04 '20 at 12:01
You changed it; you're not longer using prepared statements, opening up the code to sql injections again, and you forgot to pick the first element I guess, the query function should return an array of matches, even if it's just one. I did use `mysql2` though. — , Nov 04 '20 at 12:08
stackoverflow is great place to learn new stuff beside the original question. Honestly, prepared statements are new to me. Now I read about them and I'll never do it again, Thank you for that ( https://pastebin.com/75nzuEHt ) Stil,... the async function returns only one row for object.tasks - running the same Query directly in MySQL I get multiple. — T. Karter, Nov 04 '20 at 12:14
@DanielG I think the issue is with the `let [relatedObjects]` syntax. — T. Karter, Nov 04 '20 at 13:12

T. Karter · Answer 1 · 2020-11-04T14:40:08.887

Here is a working solution, resulting from the comments. Thank you to @Chris G.

app.get(`/details`, (req, res) => {
  const { id } = req.query;

  const query = util.promisify(connection.query).bind(connection);

  (async () => {
    try {
      let [
        object,
      ] = await query(`SELECT objects.* FROM objects WHERE id = ? LIMIT 1`, [
        id,
      ]);

      const relatedObjects = await query(
        `SELECT objects.* FROM objects WHERE relationship = ?`,
        [id]
      );
      object.relationships = relatedObjects;

      const tasks = await query(
        `SELECT tasks.* FROM tasks WHERE relationship = ?`,
        [id]
      );
      object.tasks = tasks;

      return res.json(object);
    } catch (error) {
      console.log(error);
    } finally {
      connection.end();
    }
  })();
});

How to add a second level query to axios query?

1 Answers1