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.
robertklep :
Here's another solution:\n\nvar mysql = require('mysql');\nvar conn = mysql.createConnection({\n host : 'localhost',\n database : 'test',\n});\n\nvar query = ' \\\nSELECT id, nicks.nick, GROUP_CONCAT(money) AS money \\\nFROM nicks, orders \\\nWHERE orders.nick = nicks.nick \\\nGROUP BY id';\n\nconn.query(query, function(err, rows, fields) {\n rows.forEach(function(row) {\n row.order = row.money.toString().split(',').map(function(value) {\n return { money : Number(value) };\n });\n delete row.money;\n });\n // as an example, we'll print the object as JSON\n console.log(JSON.stringify(rows, null, 2));\n});\n",
2013-12-26T07:45:54