Associating 3 tables in SEQUELIZE

Question

I am trying to associate 3 tables in sequelize.

The models I have created are as follows.

Users model

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var User = sequelize.define('User', {
    uuid: {
      type: Sequelize.STRING,
      primaryKey: true
    },
    first_name: {
      type: Sequelize.STRING,
      allowNull: false
    },
    last_name: {
      type: Sequelize.STRING,
      allowNull: false
    },
    birth_date: {
      type: Sequelize.DATE,
      allowNull: false
    },
    gender: {
      type: Sequelize.STRING,
      allowNull: true
    },
    email_id: {
      type: Sequelize.STRING,
      allowNull: false,
      validate: {
        isEmail: true
      }
    },
    contact_number: {
      type: Sequelize.STRING,
      allowNull: false,
      validate: {
        isNumeric: true
      }
    }
  }, {
    classMethods: {
      associate: function(models) {
        User.hasMany(models.UserSchool)
      }
    }
  });

  return User;
};

Schools model

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var School = sequelize.define('School', {
    school_id: {
      type: Sequelize.STRING,
      primaryKey: true
    },
    name: {
      type: Sequelize.STRING,
      allowNull: false
    },
    address: {
      type: Sequelize.TEXT,
      allowNull: false
    },
    city: {
      type: Sequelize.STRING,
      allowNull: false
    }
  }, {
    classMethods: {
      associate: function(models) {
        School.hasMany(models.UserSchool)
      }
    }
  });

  return School;
};

UserSchools model

var Sequelize = require('sequelize');

module.exports = function(sequelize, DataTypes) {
  var UserSchool = sequelize.define('UserSchool', {
    year_of_joining: {
      type: Sequelize.DATE,
      allowNull: true
    },
    year_of_passing: {
      type: Sequelize.DATE,
      allowNull: true
    },
    school_type: {
      type: Sequelize.STRING,
      allowNull: false
    },
    course: {
      type: Sequelize.STRING,
      allowNull: true
    }
  }, {
    classMethods: {
      associate: function(models) {
        UserSchool.belongsTo(models.User, {
          onDelete: "CASCADE",
          foreignKey: {
            allowNull: true
          }
        }),
        UserSchool.belongsTo(models.School, {
          onDelete: "CASCADE",
          foreignKey: {
            allowNull: true
          }
        });
      }
    }
  });

  return UserSchool;
};

When I retrieve users the userschools object is associated but school object is not to userschools. How can I create a 3 way association to retrieve the complete data?

Thanks in advance.

Let me know if there is a better way to save this kind of data in MySQL. Merging Schools and UserSchools table is not an option as I need to maintain a table with unique School Id's :) — Pradeep Rajashekar

esmrkbr esmrkbr · Accepted Answer · 2016-04-05T07:16:08

Can you try this, also?

user.js

classMethods: {
  associate: function(models) {
    // associations can be defined here
    User.belongsToMany(models.School, {
        through: {
            model: models.UserSchool
        },
        foreignKey: 'uuid'
    });
  }
}

school.js

classMethods: {
  associate: function(models) {
    //associations can be defined here
    School.belongsToMany(models.User, {
        through: {
            model: models.UserSchool
        },
        foreignKey: 'school_id'
    });
  }
}

No association in UserSchool.

Associating 3 tables in SEQUELIZE

2 Answers