mysql - 如何在单个查询中返回 node.js mysql 中的嵌套 json

Question

我正在尝试创建一个 api，它将返回一个嵌套的 json，来自两个相关的表 student 和 studentSubjects

[{
   id:"1",
   name: "John",
   subjects: [{
                id:"1",
                subject: "Math"
              },
              {
                id:"2",
                subject: "English"
              }
             ]
 },
 {
   id:"2",
   name: "Peter",
   subjects: [{
                id:"1",
                subject: "Math"
              },
              {
                id:"2",
                subject: "English"
              }
             ]
}]

我的代码是这样的:

this.get = function(res){
    db.acquire(function(err, con){                  
        con.query('SELECT * FROM students', function(err, results){ 

            if (err){                   
                res.send({status: 0, message: 'Database error'});
            }else{                  
                res.send({status: 1, data: results});                   
            }                                       
        })  
        con.release()           
    })
}

我知道查询应该有连接，但它只返回单行。我也试过做一个循环，它不会工作，因为它是异步的

感谢您的帮助!

Answer 1

您不能从 MySQL 查询创建嵌套的 JSON，因为它总是会返回一个简单的结果。

无论如何，要创建嵌套的 JSON，您应该创建多个查询并在需要的地方插入相应的数组对象。

您真的应该考虑使用 Promise 来创建嵌套查询，因为它允许您背靠背进行异步操作。如果在任何查询中发生错误，下面的代码也将关闭连接。

PS:我在下面代码的注释里解释了每一步

假设有一个名为“School”的数据库和三个名为“Student”、“Subject”和“Link_student_subject”的表。

// Instantiate mysql datase variables
const mysql = require( 'mysql' );
const config = {
    host     : 'localhost',
    user     : 'root',
    password : 'root',
    database : 'school'
  }
var connection;

// Instantiate express routing variables 
const express = require('express');
const router = express.Router();
module.exports = router;

// Wrapper class for MySQL client
// - Constructor creates MySQL connection
// - Connection opened when query is done
// - Promise is resolved when executing
// - Promise returns reject in case of error
// - If promise resolved rows will be the result
class Database {
    constructor( config ) {
        this.connection = mysql.createConnection( config );
    }
    query( sql, args ) {
        return new Promise( ( resolve, reject ) => {
            this.connection.query( sql, args, ( err, rows ) => {
                if ( err )
                    return reject( err );
                resolve( rows );
            } );
        } );
    }
    close() {
        return new Promise( ( resolve, reject ) => {
            this.connection.end( err => {
                if ( err )
                    return reject( err );
                resolve();
            } );
        } );
    }
}

// Function that will execute a query
// - In case of an error: ensure connection is always closed
// - In case of succes: return result and close connection afterwards
Database.execute = function( config, callback ) {
  const database = new Database( config );
  return callback( database ).then(
      result => database.close().then( () => result ),
      err => database.close().then( () => { throw err; } )
  );
};

// Instantiate Database
var database = new Database(config);

// Express routing
router.get('/students', function (req, res) {

  // Variables - Rows from Students & Subjects & Link_student_subject
  let rows_Students, rows_Subjects, rows_Link_Student_Subject;

  // Create a Promise chain by
  // executing two or more asynchronous operations back to back, 
  // where each subsequent operation starts when the previous operation succeeds, 
  // with the result from the previous step
  Database.execute( config,
      database => database.query( "select a.*, null as subjects from student a" )
      .then( rows => {
        rows_Students = rows;
        return database.query( "select * from subject" )
      } )
      .then( rows => {
        rows_Subjects = rows;
        return database.query( "select * from link_student_subject" )
      } )
      .then( rows => {
        rows_Link_Student_Subject = rows;
      } )
  ).then( () => {
      // Create your nested student JSON by looping on Students
      // and inserting the corresponding Subjects array
      for (let i = 0; i < rows_Students.length; i++) {
        let arraySubjects = [];
        for (let x = 0; x < rows_Link_Student_Subject.length; x++) {
            if(rows_Students[i].id == rows_Link_Student_Subject[x].id_student){
                arraySubjects.push(searchObjInArray(rows_Subjects, "id", rows_Link_Student_Subject[x].id_subject));
            }
        }
        rows_Students[i].subjects = arraySubjects;
      }
      res.send(JSON.stringify(rows_Students));
  } ).catch( err => {
      // handle the error
      res.send(err);
  });

});

// Function - search if object in array has a value and return that object
function searchObjInArray(array, arrayProp, searchVal){
    let result = null;
    let obj = array.find((o, i) => {
        if (o[arrayProp] == searchVal) {
        result = array[i];
        return true; // stop find function
        }
    });
    return result;
}

如果您使用 Node 运行此代码并转到“127.0.0.1/students”，它将返回与您的问题完全相同的 JSON。

关于 MySQL 和 promises 的所有学分和额外信息 - https://codeburst.io/node-js-mysql-and-promises-4c3be599909b