从多个表中选择封存

人气:559 发布:2022-10-16 标签: node.js mysql sequelize.js

问题描述

我目前正在开发一个使用Sequelize的系统,我需要一个从多个表中获取数据的查询,如下所示:

Select Courses.id, Rooms.DisplayLabel, Periods.DisplayName, Subjects.Name 
from Rooms, Periods,Subjects, Courses
where Periods.id = Courses.PeriodId  and Rooms.id=Courses.RoomId 
and Subjects.id = Courses.SubjectId and Courses.id = 2

房间、主题和时段是目录,课程是我保存所有钥匙的表。序列化定义如下:

module.exports = function(sequelize, DataTypes) {

  var Course = sequelize.define('Course', {
      Scholarship: {
        type: DataTypes.STRING(30)
      },
      Level: {
        type: DataTypes.INTEGER(2),
      },
      CourseType: {
        type: DataTypes.STRING(30),
      },
      RecordStatus: {
        type: DataTypes.BOOLEAN,
        default: true
      },
      DeletedAt: {
        type: DataTypes.DATE
      }
    },
    {
      associate: function(models){
        Course.belongsTo(models.School, {foreignKey: {unique: true}});
        Course.belongsTo(models.Person, {foreignKey: {unique: true}});
        Course.belongsTo(models.Period, {foreignKey: {unique: true}});
        Course.belongsTo(models.Schedule, {foreignKey: {unique: true}});
        Course.belongsTo(models.Room, {foreignKey: {unique: true}});
        Course.belongsTo(models.Subject, {foreignKey: {unique: true}});

        Course.belongsTo(models.user, { as: 'CreatedBy' });
        Course.belongsTo(models.user, { as: 'UpdateBy' });
        Course.belongsTo(models.user, { as: 'DeleteBy' });
      }
    }
  );

  return Course;
};

到目前为止,我在我的控制器上得到的唯一查询是:

exports.courseFields = function(req, res) {

    db.Course.find({
        where: {id: req.params.PeriodIdF},
        attributes: ['id'], 
        include: [{model:db.Room, attributes:['DisplayLabel']}]})
    .then(function(courses) {
            return res.json(courses);
        })
        .catch(function(err) {
            return res.render('error', {
                error: err,
                status: 500
            });
        });
};
我的问题是,如何包括其他表和字段?我搞不懂Sequelize的工作原理。

推荐答案

更新您的包含阻止

include: [
             {model:db.Room, attributes:['DisplayLabel']},
             {model:db.Periods, attributes:['DisplayLabel']},
             {model:db.Subjects, attributes:['Name']}

       ]

623