CODEWITHSUNDEEP
mysqlnode.jssequelize.js
Ellebkey
Ellebkey

Reputation: 2301

Select from multiple tables Sequelize

I'm currently developing a system using sequelize and I need a to do a query getting data from multiple tables like this:

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

Rooms, Subjects and Periods are catalogs and course is the the table where I save all the keys. The Sequelize definition is like this:

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;
};

The only query that I get so far on my controller is this:

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
            });
        });
};

My question is, how do I include the other tables and fields? I confuse how sequelize works.

Upvotes: 5

Views: 18626

Answers (1)

Keval
Keval

Reputation: 3326

Update your include block

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

Upvotes: 7

Related Questions