// Program represents an individual program activity (e.g. kayaking, mountain biking, etc.
// There are a bunch of fields on this model, none of which are relevant for this question.
var Program = sequelize.define("Program", {
name: {
type: DataTypes.STRING,
allowNull: false
},
max_participants_per_period: DataTypes.INTEGER, [...bunch of other fields...]
}, {
underscored: true,
classMethods: {
associate: function(models) {
Program.hasMany(models.ProgramPeriod);
}
}
});
// Each Program is scheduled into a ProgramPeriod. A ProgramPeriod has a start and end time.
// Different programs different durations (some are half-day, some are full-day, etc).
var ProgramPeriod = sequelize.define('ProgramPeriod', {
start_at: DataTypes.DATE,
end_at: DataTypes.DATE
}, {
underscored: true,
classMethods: {
associate: function(models) {
ProgramPeriod.belongsToMany(models.Unit, { through: 'Schedule' });
ProgramPeriod.belongsTo(models.Program);
}
}
});
// A Unit is a group of scouts and leaders. A unit will be scheduled into any number of Program through a ProgramPeriod.
// There are a bunch of other fields that are not relevant to this question.
var Unit = sequelize.define("Unit", {
unit_number: {type: DataTypes.STRING,unique: true},number_of_youth: DataTypes.INTEGER,number_of_leaders: DataTypes.INTEGER,}, {classMethods: {associate: function(models) {Unit.belongsToMany(models.ProgramPeriod, { through: 'Schedule', foreignKey: 'unit_id' });}},setterMethods: {final_payment_date: helpers.castEmptyStringToNull,number_of_youth: helpers.castEmptyStringToNull,number_of_leaders: helpers.castEmptyStringToNull},getterMethods: {total_participants: function() {return this.number_of_youth + this.number_of_leaders;}}});
┌────────────────┐ ┌─────────────────────────┐
│ │ │ ProgramPeriod │
│ Program │ ╱│ pk: id(INT) │
│ pk: id(INT) │─Program.hasMany(ProgramPeriod)───│ fK: program_id(INT) │
│ │ ╲│ references Programs.id │
└────────────────┘ │ │
└─────────────────────────┘
╲│╱
│
│
│
ProgramPeriod.belongsToMany(Unit, { through: Schedule })
│
│
│
╱│╲
┌ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┐
│ Schedule │
unit_id
│ program_period_id │
└ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ┘
╲│╱
│
│
│
Unit.belongsToMany(ProgramPeriod, { through: Schedule })
│
│
│
╱│╲
┌─────────────────────────┐
│ │
│ Units │
│ pk: id(INT) │
│ │
│ │
└─────────────────────────┘
Program.find({ where: { id: 1 }, include: [ { model: models.ProgramPeriod, include: [ models.ProgramPeriod.associations.Schedule ] } ] } )
Unit.find({ where: { id: 174 }, include: [ { model: models.ProgramPeriod, include: [models.ProgramPeriod.associations.Program] } ] })