实际上，我尝试获取我的mysql结果作为嵌套数组数据，我尝试使用从github node-mysql-nesting 获得的main.js代码多次。在想什么？为什么我需要使用该函数将那些查询结果转换为嵌套数组，任何人都知道可以在这些段上为我提供帮助
let options = { sql: "SELECT
units.name as unit_name,units.id as unit_id,units.description as unit_description,classes.name as class_name,classes.academic_year as class_academic_year,semesters.name as semester,standards.name as standard,branches.name as branch,sections.name as section,chapters.id as chapter_id,chapters.name as chapter_name,chapters.unit_id as chapter_unit_id,chapters.description as chapter_description
FROM units
LEFT JOIN chapters ON chapters.unit_id = units.id
JOIN classes ON classes.id = units.class_id
JOIN courses ON courses.id = units.course_id
JOIN users ON users.id = units.created_by
JOIN branches ON branches.id = classes.branch_id
JOIN semesters ON semesters.id = classes.semester_id
JOIN sections ON sections.id = classes.section_id
JOIN standards ON standards.id = classes.standard_id",nestTables: true };
database.query(options).then(([classes,classesFields]) => {
if (classes) {
var func = require('../../config/main')
let data = {
nested: func.convertTonested(classes,nestingOptions),original: classes
}
res.send(Response.successResponseWithData(SuccessMessage.DataFetched,data))
}
else {
res.send(Response.failureResponse(FailureMessage.DataFetchFailed))
}
}).catch(err => {
console.log(err)
res.send(Response.failureResponse(FailureMessage.DataFetchFailed))
})
我得到的是
{
"status": true,"status_code": 200,"message": "DATA FETCHED SUCCESSFULLY","data": {
"nested": [
{
"unit_name": "UNIT I","unit_id": 1,"unit_description": "UNIT I Delivers all contents "
},{
"unit_name": "UNIT II","unit_id": 2,"unit_description": "UNIT II Delivers all contents "
}
],"original": [
{
"units": {
"unit_name": "UNIT I","unit_description": "UNIT I Delivers all contents "
},"classes": {
"class_name": "I Standard B Section","class_academic_year": 2020
},"semesters": {
"semester": "I"
},"standards": {
"standard": "1st Standard"
},"branches": {
"branch": "Computer Science"
},"sections": {
"section": "A"
},"chapters": {
"chapter_id": 7,"chapter_name": "MARS","chapter_unit_id": 1,"chapter_description": "Mars is the fourth planet from the Sun and the second-smallest planet in the Solar System after Mercury. In English,Mars carries a name of the Roman god of war and is often referred to as the 'Red Planet'.[15][16] The latter refers to the effect of the iron oxide prevalent on Mars' surface,which gives it a reddish appearance distinctive among the astronomical bodies visible to the naked eye.[17] Mars is a terrestrial planet with a thin atmosphere,having surface features reminiscent both of the impact craters of the Moon and the valleys,deserts,and polar ice caps of Earth."
}
},{
"units": {
"unit_name": "UNIT I","chapters": {
"chapter_id": 8,"chapter_name": "Jupiter","chapter_description": "Jupiter is the fifth planet from the Sun and the largest in the Solar System. It is a gas giant with a mass one-thousandth that of the Sun,but two-and-a-half times that of all the other planets in the Solar System combined. Jupiter is one of the brightest objects visible to the naked eye in the night sky,and has been known to ancient civilizations since before recorded history. It is named after the Roman god Jupiter.[18] When viewed from Earth,Jupiter can be bright enough for its reflected light to cast shadows,[19] and is on average the third-brightest natural object in the night sky after the Moon and Venus."
}
},{
"units": {
"unit_name": "UNIT II","unit_description": "UNIT II Delivers all contents "
},"chapters": {
"chapter_id": null,"chapter_name": null,"chapter_unit_id": null,"chapter_description": null
}
}
]
}
}
我的期望是
{
"status": true,"unit_description": "UNIT I Delivers all contents "
"chapters": [
{
"chapter_id": 7,and polar ice caps of Earth."
},{
"chapter_id": 8,[19] and is on average the third-brightest natural object in the night sky after the Moon and Venus."
}
],},"chapter_description": null
}
}
]
}
}
在我的main.js中
/**
* Kyle Ladd,2015
* See project's Github page for usage details.
* https://github.com/kyleladd
*/
/**
* Originally forked from
* Selman Kahya,2013
* See project's Github page for usage details.
* https://github.com/Selmanh
*/
/**
* takes nested mysql result array as a parameter,* converts it to a nested object
*
* @param {Array} rows
* @param {Array} nestingOptions
* @return {Object}
*/
exports.convertTonested = function (rows,nestingOptions) {
if (rows == null || nestingOptions == null)
return rows;
var levels = nestingOptions;
// put similar objects in the same bucket (by table name)
var buckets = new Array();
for (var i = 0; i < levels.length; i++) {
var result = new Array();
var level = levels[i];
var pkey = level.pkey;
var tableName = level.tableName;
for (var j = 0; j < rows.length; j++) {
var object = rows[j][tableName];
// check if object has key property
if (object == null){
console.log("Error: couldn't find " + tableName + " property in mysql result set")
continue;
}
// if object isn't in result array,then push it
if(!isExist(result,pkey,object[pkey]) && object[pkey] != null)
result.push(object);
}
// Buckets should have two properties,a table name (to identify for relationships) and values.
buckets.push({table:tableName,values:result});
}
// we have similar objects in the same bucket
// now,move lower level objects into related upper level objects where relationship key values match
for (var i = buckets.length-1; i >= 1; i--) {
// For each upper bucket
for (var u = i-1; u >= 0; u--) {
if(levels[u].hasOwnProperty('fkeys')){
//Go through upper buckets foreign keys
for (var f = 0; f < levels[u].fkeys.length; f++) {
// if upper bucket has a foreign key to this bucket's table
if(buckets[i].table == levels[u].fkeys[f].table){
// For each element in this table
for (var ft = 0; ft < buckets[i].values.length; ft++){
// Go through each element in upper table matching values with this bucket's values
for (var utv = 0; utv < buckets[u].values.length; utv++){
// Relationship match
if(buckets[i].values[ft][levels[i].pkey] == buckets[u].values[utv][levels[u].fkeys[f].col]){
// Shouldn't be a list because there can only one,fk can only match one pk
buckets[u].values[utv][buckets[i].table] = buckets[i].values[ft];
}
}
}
}
}
}
}
//CHECK TO SEE IF THIS BUCKET HAS THE FOREIGN KEY USED IN AN UPPER LEVEL
if(levels[i].hasOwnProperty('fkeys')){
for (var cf = 0; cf < levels[i].fkeys.length; cf++) {
// For each upper bucket
for (var ub = i-1; ub >= 0; ub--) {
if( levels[i].fkeys[cf].table == levels[ub].tableName){
// For each element in this table
for (var ct = 0; ct < buckets[i].values.length; ct++){
// Go through each element in matching table
for (var utbv = 0; utbv < buckets[ub].values.length; utbv++){
if(buckets[i].values[ct][levels[i].fkeys[cf].col] == buckets[ub].values[utbv][levels[ub].pkey]){
//If there is a match,create an empty list with the tablename if it doesn't already exist
if(!buckets[ub].values[utbv].hasOwnProperty(levels[i].tableName)){
buckets[ub].values[utbv][levels[i].tableName] = [];
}
// Append object where relationship key values match
buckets[ub].values[utbv][levels[i].tableName].push(buckets[i].values[ct]);
}
}
}
}
}
}
}
}
// at the end,we have all the nested objects in the first bucket
return buckets[0].values;
};
/**
* checks if one of the objects in an array has the given property,* and the value of that property equals to given value
*
* @param {Array} array
* @param {String} key
* @param {String} value
* @return {Array}
*/
//
var isExist = function(array,key,value){
for (var i = 0; i < array.length; i++) {
if(array[i][key] == value)
return true;
}
return false;
}