javascript - 如何使用 Sequelize.js 解析复杂的查询操作？

问题描述

我有一个Option表，它有一个question_id作为外键的表Questions。然后在Questions表中我有 2 个外键，即question_category_id和section_id。对于第一个选项部分，我可以应用LEFT OUTER JOIN查询，但我还需要获取Question_Category和Section表的值。让我首先弄清楚我希望我的输出如何：

输出 JSON

"questions": [
  {
    "id": 9,
    "isActive": true,
    "question_text": "What is abc ?",
    "createdBy": "avis",
    "questionCategory": {
      "id": 1,
      "name": "aptitude"
    },
    "section": {
      "id": 1,
      "marks": 5
    },
    "options": [
      {
        "id": 1,
        "answer": true,
        "option_text": "A",
        "question_id": 9
      },
      {
        "id": 2,
        "answer": false,
        "option_text": "B",
        "question_id": 9
      }
    ]
  }
]

现在我正在指定我的数据库模型：

question_category.js

module.exports = (sequelize, Sequelize) => {
    const QuestionCategory = sequelize.define('question_category', {
        id:{ type: Sequelize.BIGINT, autoIncrement: true, allowNull: false, primaryKey: true }, 
        isActive: { type: Sequelize.BOOLEAN, allowNull: false },
        question_category_name: { type: Sequelize.STRING, allowNull: false },
        createdBy: { type: Sequelize.STRING, allowNull: false },
        createdAt: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    }, { timestamps: false });

    return QuestionCategory;
};

节.js

module.exports = (sequelize, Sequelize) => {
    const Section = sequelize.define('section', {
        id: { type: Sequelize.BIGINT, autoIncrement: true, allowNull: false, primaryKey: true },
        isActive: { type: Sequelize.BOOLEAN, allowNull: false },
        marks_per_question: { type: Sequelize.INTEGER, allowNull: false },
        createdBy: { type: Sequelize.STRING, allowNull: false },
        createdAt: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    }, { timestamps: false });

    return Section;
};

问题.js

module.exports = (sequelize, Sequelize) => {
    const Questions = sequelize.define('questions', {
        id:{ type: Sequelize.BIGINT, autoIncrement: true, allowNull: false, primaryKey: true }, 
        isActive: { type: Sequelize.BOOLEAN, allowNull: false },
        question_text: { type: Sequelize.STRING, allowNull: false },
        createdBy: { type: Sequelize.STRING, allowNull: false },
        createdAt: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    }, { timestamps: false });

    return Questions;
};

option.js

module.exports = (sequelize, Sequelize) => {
    const Options = sequelize.define('options', {
        id:{ type: Sequelize.BIGINT, autoIncrement: true, allowNull: false, primaryKey: true }, 
        answer: { type: Sequelize.BOOLEAN, allowNull: true },
        option_text: { type: Sequelize.STRING, allowNull: false },
        createdAt: { type: Sequelize.DATE, allowNull: false, defaultValue: Sequelize.literal('CURRENT_TIMESTAMP') }
    }, { timestamps: false });

    return Options;
};

在database.js即用于导出模型的主 js 文件中，我已将模型关联如下：

const dbConfig = require('../config/db.config');
const Sequelize = require('sequelize');

const sequelize = new Sequelize(
    dbConfig.DB, dbConfig.USER, dbConfig.PASSWORD,
    {
        host: dbConfig.HOST,
        port: dbConfig.PORT,
        dialect: 'mysql',
        operatorsAliases: 0
    }
);

sequelize.authenticate().then(() => {
console.log('Connection has been established successfully.');
}).catch(err => {
    console.error('Unable to connect to the database:', err);
});

const db = {};

db.Sequelize = Sequelize;
db.sequelize = sequelize;

db.QuestionCategory = require('./question_model/question_category')(sequelize, Sequelize);
db.Section = require('./question_model/section')(sequelize, Sequelize);
db.Question = require('./question_model/question')(sequelize, Sequelize);
db.Option = require('./question_model/option')(sequelize, Sequelize);

// Relating Question Category with Questions
db.QuestionCategory.hasMany(db.Question, {
    foreignKey: 'question_category_id',
    sourceKey: 'id'
});
db.Question.belongsTo(db.QuestionCategory, {
    foreignKey: 'question_category_id',
    targetKey: 'id'
});

// Relating Sections with Questions
db.Section.hasMany(db.Question, {
    foreignKey: 'section_id',
    sourceKey: 'id'
});
db.Question.belongsTo(db.Section, {
    foreignKey: 'section_id',
    targetKey: 'id'
});

// Relating Questions with Options
db.Question.hasMany(db.Option, {
    foreignKey: 'question_id',
    sourceKey: 'id'
});
db.Option.belongsTo(db.Question, {
    foreignKey: 'question_id',
    targetKey: 'id'
});

这就是我的结构。现在为了实现上述输出格式，我编写了以下逻辑，但它没有输出正确的 JSON：

const db = require('../models/database');
const errors = require('../config/errors').errors;

exports.viewQuestion = (req, res, next) => {
  try {
    db.Question.findAll({ 
      attributes: { exclude: ['createdAt','section_id','question_category_id'] },
      include: [{
        model: db.Option,
        attributes: { exclude: ['createdAt'] }
      }]
    }).then(data => {
        if(data.length == 0) {
          return res.status(200).send({
            status: 200,
            questions: 'No Data'
          });
        }
        db.QuestionCategory.findAll({
          attributes: { exclude: ['createdBy','createdAt','isActive'] },
          include: db.Question,
          attributes: { exclude: ['id','isActive','question_text','createdBy','createdAt','section_id'] }
        }).then(question_category => {
          Object.assign(data[0], { 'questionCategories': question_category });
          res.status(200).send({
            status: 200,
            questions: data
          });
        });
      }).catch(err => {
        return res.status(204).send(errors.MANDATORY_FIELDS);
      });
  } catch(err) {
    return res.status(204).send(errors.MANDATORY_FIELDS);
  }
};

我还没有写出Section部分的逻辑，因为我要一步一步来。通过编写此逻辑得到的输出是：

{
    "status": 200,
    "questions": [
        {
            "id": 9,
            "isActive": true,
            "question_text": "What is abc ?",
            "createdBy": "avis",
            "options": [
                {
                    "id": 1,
                    "answer": true,
                    "option_text": "A",
                    "question_id": 9
                },
                {
                    "id": 2,
                    "answer": false,
                    "option_text": "B",
                    "question_id": 9
                }
            ]
        }
    ]
}

questionCategories没有反映在输出中。

请帮助我，因为我有更多这样的场景，我可以根据这个解决所有问题。

标签： javascriptmysqlnode.jsdatabasesequelize.js

javascript - 如何使用 Sequelize.js 解析复杂的查询操作？

问题描述

解决方案

推荐阅读