node.js / javascript add an array inside other array as child

Question

Description: I do a mysql query to a table, and get back some rows

>   connection.query(q2,function(err,rows){...}

lets say the rows looks like {id:",,,", time:"..." etc:"cc"} Next for every row i query another table and get back some lets say coms based on the id of the row. there can be 0 - 100 rows depenting on the query

> for (var i = 0; i < rows.length; i++){   
> connection.query(qc,function(err2,coms){

lets say the coms looks like this { rowid:",,,", something:"ddd" smthelse:"ff"} there can also be 0 - 100 coms in every row Question:

How can i add coms inside rows, so for every row i have the coms i got back from the table? i want something like this

> {id:"1", time:"..." etc:"cc" coms:{ rowid:",,,", something:"ddd"
> smthelse:"ff"}}

Allready tried: rows[i].comms=coms; (not working) rows[i].comms[j]=coms[j]; (not working) rows[i].push(coms) (not working) rows.comms=coms; (working but its not what i want. with this i get multiple times the same data)

the whole code i am using after the first query

 connection.query(q2,function(err,rows){
                    //      connection.release();
                            if(!err) {
                            //      rows["coms"]=[];
                                    for (var i = 0; i < rows.length; i++){
                                            var qc="select * from comms where id='"+rows[i].id+"' order by com_id";
                                            connection.query(qc,function(err2,coms){
                                             if(!err2) {
                                             // rows[i].cid=coms.com_id;
                                             //     rows[i].coms=[];
                                                    rows[i]="coms";
                                                    for (var j = 0; j < coms.length; j++){
                                                    // data[i][j]=coms[j];
                                              //    rows[i].coms[j]=coms[j];
                                                    //  rows[i][j].coms=coms[j];
                                                    }
                                    //         data[i]=coms;
                                             // rows[i]["coms"]=coms;
                                    //        console.log(rows);                                
                                             }
                                            });
                                    }
                            //      rows.push(data);
                                      console.log(rows);
                                    users[index].emit('data',rows);
                            }
                            connection.release();
                    });

Answer 1

Why don't you change your second query's WHERE id = row[i].id to WHERE id IN(ids) to get your data with one pass. It's easier on the sql server and it's easier to handle the code. First get a list of Ids to pass to the IN() condition. when the data come back, you can iterate through the parent rows and filter the children node rows and append to the parent as the property you wanted. you may want to use underscorejs library to help you with collection operations.

combining all of you sub-sequence selects into one select will speed up your queries; because it will get rid of all the over heads that are needed to make connections and queries to sql server down to one connection and one query. Imagine if your first query returns 100, 1000, 10,000 rows. Your sql server will take a performance hit with the number of sub-sequence queries.

I don't believe that there's a maximum length for a query; so you can go crazy with the number of Ids inside of the IN() condition.

Answer 2

I don't have any MySQL database available to test this, but I hope it gets the point across or gives some good ideas.

var Promise = require("bluebird");

// This is a "promisified" version of connection.query
function query(con, sql) {
  return new Promise(function(resolve, reject) {
    con.query(sql, function(err, rows) {
      if (err) { reject(new Error(err)); }
      else { resolve(rows); }
    });
  });
}

Your code would now translate to something like this:

query(connection, q2)
    .then(function(rows) {
        return Promise.map(rows, function(row) {
            var qc = "select * from comms where id='"+row.id+"' order by com_id";
            return query(connection, qc).then(function(coms) {
                // Do whatever you want with result of second query here
                row.coms = coms;
                return row;
            })
        })
    })
    .then(function(rows) {
        // All promises have resolved here, rows is an array of values
        users[index].emit('data', rows);
    })
    .catch(function(err) {
        // Handle errors here
    });