如何在Node.js中重构查询结果为树形结构（TreeView）？

问题背景 #

在Node.js中使用Sequelize进行多表关联查询时，当前返回扁平结构结果，需要重构为指定的嵌套树形结构。

现有代码 #

业务代码 #

const getquestionnaire = await QuestionnaireModels.getQuestion(yearlevel, section);
res.send(getquestionnaire);

getQuestion方法代码 #

async getQuestion(yearlevel, section) {
    let student = await Database.getConnection().models.student;
    let qgroup = await Database.getConnection().models.questiongroups;
    let qdetails = await Database.getConnection().models.questiondetails;
    let oitem = await Database.getConnection().models.optionitems;
    student.hasMany(qgroup, { foreignKey: '_id'});
    student.hasMany(qdetails, {foreignKey: 'QuestionGroupID'})
    student.hasMany(oitem, {foreignKey: 'QuestionDetailsID'})
    let question = await Database.getConnection().models.student.findAll({
     attributes:[
    'yearlevel','section','questiongroups.GroupName','questiondetails.QuestionDescription','optionitems.OptionDescription'
    ],
      where: {
        yearlevel: yearlevel,
        section: section
      },
      include:[
        { model: qgroup, attribute:[] },
        { model: qdetails, attribute:[] },
        { model: oitem, attribute:[] },
    ],
      raw: true,
    })
    return result
  },

当前返回结果 #

[
    {
        "yearlevel": "Grade 7",
        "section": "Apple",
        "Groupname": "Third",
        "QuestionDescription":"Eating Disorder",
        "OptionDescription": "Test"
    },
    {
        "yearlevel": "Grade 8",
        "section": "Blue",
        "Groupname": "Test Group name",
        "QuestionDescription":"Test Question",
        "OptionDescription": "Test"
    }
]

期望树形结构结果 #

[
    {
        "yearlevel": "Grade 7",
        "section": "Apple",
        "question":{
            "Groupname": "Third",
            "groupdetail":{
                "QuestionDescription":"Eating Disorder",
                "optionItems":{
                    "OptionDescription": "Test"
                }
            }
        }
    },
    {
        "yearlevel": "Grade 8",
        "section": "Blue",
        "question":{
            "Groupname": "Test Group name",
            "groupdetail":{
                "QuestionDescription":"Test Question",
                "optionItems":{
                    "OptionDescription": "Test"
                }
            }
        }
    }
]

解决方案 #

1. 修正模型关联关系 #

原代码错误地让student直接关联所有子表，正确的关联应该是层级嵌套：student -> questiongroups -> questiondetails -> optionitems，每个层级通过对应外键关联。

2. 修改查询逻辑并转换结构 #

去掉raw: true（该配置会强制返回扁平数据），通过嵌套include获取层级化数据，最后将Sequelize返回的模型实例转换为期望的树形结构：

async getQuestion(yearlevel, section) {
    // 解构获取模型
    const { student, questiongroups: qgroup, questiondetails: qdetails, optionitems: oitem } = Database.getConnection().models;

    // 设置正确的层级关联
    student.hasMany(qgroup, { foreignKey: 'studentId' }); // 确保qgroup表中外键字段与student主键匹配，比如student.id对应qgroup.studentId
    qgroup.hasMany(qdetails, { foreignKey: 'QuestionGroupID' });
    qdetails.hasMany(oitem, { foreignKey: 'QuestionDetailsID' });

    // 查询层级化数据
    const studentList = await student.findAll({
        attributes: ['yearlevel', 'section'],
        where: { yearlevel, section },
        include: [
            {
                model: qgroup,
                attributes: ['GroupName'],
                include: [
                    {
                        model: qdetails,
                        attributes: ['QuestionDescription'],
                        include: [
                            {
                                model: oitem,
                                attributes: ['OptionDescription']
                            }
                        ]
                    }
                ]
            }
        ]
    });

    // 转换为期望的树形结构
    return studentList.map(item => {
        // 示例假设每个student对应一个分组、一个问题、一个选项，若存在多个可改用循环处理
        const group = item.questiongroups[0];
        const detail = group.questiondetails[0];
        const option = detail.optionitems[0];
        
        return {
            yearlevel: item.yearlevel,
            section: item.section,
            question: {
                Groupname: group.GroupName,
                groupdetail: {
                    QuestionDescription: detail.QuestionDescription,
                    optionItems: {
                        OptionDescription: option.OptionDescription
                    }
                }
            }
        };
    });
}

关键说明 #

• 关联关系必须匹配数据库表的实际外键字段，比如qgroup表中关联student的外键如果不是_id，需要替换为正确字段（示例中用studentId）。
• 若存在一个学生对应多个分组、一个分组对应多个问题的情况，需要调整转换逻辑，比如将question改为数组，用map遍历item.questiongroups生成多个结构。
• 去掉raw: true后，Sequelize会返回模型实例对象，可通过get({ plain: true })转为纯JSON对象（如果需要），示例中直接访问属性即可。

内容的提问来源于stack exchange，提问作者denise