Question: Sequelize associations for a scheduling app

[grahamb]

I posted this on the Google Group five days ago and didn't get any takers, so I figured I'd try here:

I'm working on an app that handles program scheduling for a large scout camp. The primary models I'm concerned with are:

// 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)       │                
                                                    │                         │                
                                                    │                         │                
                                                    └─────────────────────────┘                

With this setup, I'm able to query for all the Units assigned to a Program's ProgramPeriod:

Program.find({ where: { id: 1 }, include: [ { model: models.ProgramPeriod, include: [ models.ProgramPeriod.associations.Schedule ] } ] } )

…and I can query for aUnit's schedule of Programs:

Unit.find({ where: { id: 174 }, include: [ { model: models.ProgramPeriod, include: [models.ProgramPeriod.associations.Program] } ] })

I'm having trouble with figuring out a few things needed for scheduling. Assume that unit id 174 has 10 participants. I need to, for any given Program, query for all ProgramPeriods that have enough space available, and don't conflict with any other ProgramPeriod time already on the Unit's schedule.

I'm not tied to any of the above associations; I can change things around if it makes accomplishing this easier.

Thanks!

[janmeier]

I doubt this can be written directly in sequelize, but to find periods without overlap, you could do something like:

SELECT * FROM programPeriod p1 WHERE NOT EXIST(
  SELECT * FROM programPeriod p2 WHERE p1 overlaps with p2
)
JOIN on schedule
JOIN on program

[grahamb]

Should have mentioned: this is Postgres. I'm getting a syntax error with the above query:

# SELECT * FROM "ProgramPeriods" p1 WHERE NOT EXIST(SELECT * FROM "ProgramPeriods" p2 WHERE p1 overlaps with p2) JOIN on "Schedule" JOIN on "Programs";

ERROR:  syntax error at or near "SELECT"
LINE 1: SELECT * FROM "ProgramPeriods" p1 WHERE NOT EXIST(SELECT * F...

[janmeier]

Well, something similar then - I'm not completely versed in PG syntax :)

Closing this issue, since it is neither a bug report nor a feature request.

For general sequelize questions, please use StackOverflow or Google groups. - I know you already posted on google groups, but the issues queue should be limited to bugs and feature request