Matt Aft Matt Aft - 2 days ago 3
Javascript Question

How to query for info from 2 tables with sequelize

So I have two tables: Class and Course. Each Class has a course_id that defines what type of course it is on the Course table. Here are my models:

const Class = sequelize.define('class', {
class_date: Sequelize.DATE,
begin_time: Sequelize.TIME,
end_time: Sequelize.TIME,
max_capacity: Sequelize.INTEGER,
is_published: Sequelize.BOOLEAN,
training_facility_id: Sequelize.INTEGER,
state_id: Sequelize.INTEGER,
registration_deadline: Sequelize.DATE,
course_id: Sequelize.INTEGER,
is_report_60_generated: Sequelize.BOOLEAN,
completed_by_user_id: Sequelize.INTEGER
}, {
timestamps: false,
freezeTableName: true
})

const Course = sequelize.define('course', {
code: Sequelize.STRING,
name: Sequelize.STRING
}, {
timestamps: false,
freezeTableName: true
})


When I hit the classes endpoint, I get a array of classes. How would I set up the query to respond with the course name and code for each class instead of just the course_id?

Answer

You will need to create a relationship between the Class and Course models. If you specify the underscored: true option in the Model definition it won't use camel case and will automatically create the Class.course_id column, so you don't need to define it (the same probably goes for completed_by_user_id).

const Class = sequelize.define('class', {
  class_date: Sequelize.DATE,
  begin_time: Sequelize.TIME,
  end_time: Sequelize.TIME,
  max_capacity: Sequelize.INTEGER,
  is_published: Sequelize.BOOLEAN,
  training_facility_id: Sequelize.INTEGER,
  state_id: Sequelize.INTEGER,
  registration_deadline: Sequelize.DATE,

  // this will be auto-created by the relationship
  // course_id: Sequelize.INTEGER,

  is_report_60_generated: Sequelize.BOOLEAN,
  // you probably want a relationship here as well
  completed_by_user_id: Sequelize.INTEGER
}, {
  timestamps: false,
  freezeTableName: true,

  // use underscored names
  underscored: true,
})

const Course = sequelize.define('course', {
  code: Sequelize.STRING,
  name: Sequelize.STRING
 }, {
  timestamps: false,
  freezeTableName: true,
  underscored: true,
})

Based on your data model it seems as though each Class will require an associated Course with different attributes like start time, etc, so you will tell Sequelize that each Class belongsTo() a Course.

// tell Sequelize that once course will be assigned to many classes, and it is required (not null)
Class.belongsTo(Course, { foreignKey: { allowNull: false } })

Once they are related you can use the include option value to specify a model to join to your primary query. If you use as in the relationship you must specify it here as well.

// do a joined query using "include"
Class.findAll({
  include: [
    {
      model: Course
    }
  ]
})
Comments