use nodejs to query mysql database

Question

I use mysql module nodejs-mysql I have two tables, Their struct is like this:

Table nicks

id   |nick   |
--------------
1    |Arnold |
2    |Bob    |

Table order

nick   |money |
---------------
Arnold |12    |
Arnold |43    |
Arnold |3     |
Bob    |32    |
Bob    |2     |

I want get a json object whose struct is like this:

[
   {id:1, nick:'Arnold', order:[{money:12},{money:43},{money:3}]},
   {id:2, nick:'Bob', order[{money:32},{money:2}]}
]

so what should I do?I use nodejs

what I have try:

   var mysql      = require('mysql');
   var connection = mysql.createConnection({
      host     : 'example.org',
      db       : 'db'
      user     : 'user',
      password : 'secret'
   });
   connection.connect();
   connection.query('select * from nicks',function(err,data){
       //here I travese the data array,and select the order table to get the money filed data.

   });

I know how to create a query with node.js, I just don't know a method to get the results I want.I don't know how to make a proper query.

Solution

Here's another solution:

var mysql = require('mysql');
var conn  = mysql.createConnection({
  host      : 'localhost',
  database  : 'test',
});

var query = ' \
SELECT id, nicks.nick, GROUP_CONCAT(money) AS money \
FROM nicks, orders \
WHERE orders.nick = nicks.nick \
GROUP BY id';

conn.query(query, function(err, rows, fields) {
  rows.forEach(function(row) {
    row.order = row.money.toString().split(',').map(function(value) {
      return { money : Number(value) };
    });
    delete row.money;
  });
  // as an example, we'll print the object as JSON
  console.log(JSON.stringify(rows, null, 2));
});

Comments

[Arnold]

Thank you, It's a feasible method, but is there a way I can do it completely?

[robertklep]

What do you mean by 'completely'?

[Arnold]

I mean 'directly', is there a method that I can query the stucts I want without split strings?

[robertklep]

@Arnold I don't think MySQL supports that, so in that case you'd have to leave out the GROUP BY id clause and parse the results yourself.

[Arnold]

Oh, I see. Thank you very much.

[Z2VvZ3Vp]

Did not answer the question of trying to get the joined rows into the single record row.