从多个表中选择封存
我目前正在开发一个使用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']}
]
相关文章