How to execute two mysql queries using promise in nodejs?

Question

Here is what i going to achieve, i want to have an JSON data that returned from my node.js server is joined based on the value of first mysql queries (array JSON data)

if i just want to execute two mysql queries i just enable multipleStatements: true then the code will be like this :

app.post('/product', function (req, res) {

connection.query('call getProductList; call rowCountTotal', function (err, rows, fields) {
    if (!err) {
        var response = [];

        if (rows.length != 0) {
            response.push({ 'result': 'success', 'data': rows });
        } else {
            response.push({ 'result': 'error', 'msg': 'No Results Found' });
        }

        res.setHeader('Content-Type', 'application/json');
        res.status(200).send(JSON.stringify(response));
    } else {
        res.status(400).send(err);
    }
});

});

than the data will showed up two JSON that are separated in two arrays, but what i want to build here is one JSON with multiple array JSON data, which is looked like this :

Sample JSON that i want :

[  
   {  
      "product_id":"1",
      "product_name":"MX-001",
      "product_attachment":[  
         {  
            "product_id":"1",
            "product_attachment_id":"1",
            "file_name":"maxgrand5.jpg",
            "file_path":"assets"
         }
      ]
   }
]

And here is what i trying to do in my node.js server side code, i trying to use

Promise.all (i think this code i should use right?) :

    return new Promise(function(resolve, reject) {

        Promise.all(connection.query('call getProductSingle("'+ product_series +'")', function (err, rows, fields) {
            if (!err) {
                var response = [];

                if (rows.length != 0) {
                    response.push({ 'result': 'success', 'data': rows });
                } else {
                    response.push({ 'result': 'error', 'msg': 'No Results Found' });
                }

                connection.query('call getProductAttachment("'+ rows[0][0].product_id +'")', function (err, rowsAttachment, fields) {
                    if (!err) {
                        console.log("second query");
                        if (rowsAttachment.length != 0) {
                            response.push({'product_attachment': rowsAttachment });
                        } else {
                            response.push({ 'result': 'error', 'msg': 'No Results Found' });
                        }
                    }
                });
                console.log("outside second query");
                res.setHeader('Content-Type', 'application/json');
                res.status(200).send(JSON.stringify(response));
            } else {
                res.status(400).send(err);
            }

            console.log("last");
            if (err) {
                return reject(err);
            }
            resolve(res);
        }));
    });

here is my Stored Procedure result which named in 'getProductSingle' :

product_id = 1
product_name = MX-001

and here is my second procedure result 'getProductAttachment' :

product_id = 1
file_name = maxgrand5.jpg
file_path = assets
product_attachment_id = 1

one single product_id can have more than 1 product_attachment_id

how can i get the data joined?

I just updated my question, the problem is the second query is too late when i make the request, i should use promise to make it not late, how to do this?

You should create a query (possible a stored function) hat returns the results you need.... — Usagi Miyamoto
– Usagi Miyamoto, Commented Aug 24, 2017 at 4:06
@UsagiMiyamoto do you have example stored function can create a JSON tree like that? but i ever read promise.all can do that — Ke Vin
– Ke Vin, Commented Aug 24, 2017 at 8:14
Why don't you use a query or stored procedure, which is JOINing the information from both tables. With that you can iterate through the rows of that result table... — Myonara
– Myonara, Commented Aug 25, 2017 at 14:23
@Myonara can you show me a stored procedure query sample to produce nested JSON? — Ke Vin
– Ke Vin, Commented Aug 28, 2017 at 2:48

Myonara · Accepted Answer · 2017-08-28 08:47:11Z

First I created the query, in which the product_single table is joined to the product_attachments, maybe you want to restrict it with an WHERE clause or a paging mechanism with LIMIT and OFFSET:

SELECT ps.product_id, ps.product_name, pa.product_attachment_id,
pa.file_name, pa.file_path
FROM product_single ps
LEFT JOIN product_attachment pa
ON ps.product_id = pa.product_id
ORDER by ps.product_id,pa.product_attachment_id;

In the following code I will refer to this query with a call product_join_att.

return new Promise(function(resolve, reject) {
    var result_products = [];
    var result_attachments = [];
    var old_id = undefined;
    var old_name = undefined;
    var new_id = undefined;
    var row_index = 0;
    connection.query('call product_join_att', function (err, rows, fields) {
        if (err) {
            reject(err);
            return;
        } else if (rows.length == 0) {
            reject(new Error('No Results found'));
            return;
        }
        while (row_index < rows.length  ) {
            new_id = rows[row_index].product_id;
            if (old_id !== new_id) { // any new line with an new id...
                if (typeof old_id !== 'undefined') { // when the old line is existing
                    result_products.push( // push the product
                        {"product_id": old_id.toString(),
                         "product_name":old_name,
                         "product_attachment": result_attachments
                        });
                }
                old_id = new_id; // remember the new_id
                old_name = rows[row_index].product_name;// and name
                product_attachments = []; // and initialize attachments.
            }
            product_attachments.push({ // provide the inner attachment informations.
                "product_id": new_id,
                "product_attachment_id" : rows[row_index].product_attachment_id,
                "file_name" : rows[row_index].file_name;
                "file_path" : rows[row_index].file_path;
            });
            row_index++; // and go to the next row.
        }
        if (typeof old_id !== 'undefined') { // if there are still data
            result_products.push( // push the last line also.
                {"product_id": old_id.toString(),
                 "product_name":old_name,
                 "product_attachment": result_attachments
                });
        }
    } // query
    resolve(result_products);
} // end of promise...

thank you @Myonara i think i can do it without promise way, i have my code that work below, but since i ask about to do the JSON array tree with "promise" way, i have your answer as my solution, thank you so much

Ke Vin · Accepted Answer · 2017-08-29 03:19:18Z

i figure it out with simplier solution but this way is not a "promise" way to get it done, so decide it which to use if you guys face the problem like this. Since i dont need many data to loop, just a single root array JSON with one dimension JSON array, i will just put it this way :

app.post('/getsingleproduct', function (req, res) {
    var product_series = req.body.product_series;

    connection.query('call getProductSingle("'+ product_series +'")', function (err, rows, fields) {
        if (!err) {
            var response = [];

            if (rows.length != 0) {
                response.push({ 'result': 'success', 'data': rows[0] });
            } else {
                response.push({ 'result': 'error', 'msg': 'No Results Found' });
            }

            connection.query('call getProductAttachment("'+ rows[0][0].product_id +'")', function (err, rowsAttachment, fields) {
                if (!err) {

                    if (rowsAttachment.length != 0) {

                        response[0].data[0]['product_attachment']= rowsAttachment[0];

                        res.setHeader('Content-Type', 'application/json');
                        res.status(200).send(JSON.stringify(response));
                    } else {
                        response.push({ 'result': 'error', 'msg': 'No Results Found' });
                        res.status(400).send(err);
                    }
                }
            });

        } else {
            res.status(400).send(err);
        }
    });

});

this is a non-promise way, if you need promise way, look for @Myonara answer, i think that the best

Collectives™ on Stack Overflow

How to execute two mysql queries using promise in nodejs?

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related