Join two tables via a third table in sequelize

user_3719167 0
Viewed 428

I'm using nodejs and Sequelize

I have two models like

user-plan.model.js

class UserPlan extends Sequelize.Model {
}

UserPlan.init({
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true
    },
    user_id: {
        type: Sequelize.INTEGER,
        allowNull: false
    },
    plan_id: {
        type: Sequelize.INTEGER,
        allowNull: false
    },
    expire: {
        type: Sequelize.DATE,
        allowNull: true
    },
    active: {
        type: Sequelize.BOOLEAN,
        allowNull: true
    }
}, {
    sequelize,
    modelName: 'user_plan',
    tableName: 'plans_userplan'
});

quota.model.js

class Quota extends Model {
}

Quota.init({
    id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true
    },
    codename: {
        type: Sequelize.STRING,
        allowNull: false
    }
}, {
    sequelize,
    modelName: 'quota',
    tableName: 'plans_quota'
});

These two tables are joined through a third table

plan-quota.model.js

class PlanQuota extends Model {
}

PlanQuota.init({
    id: {
        type: Sequelize.INTEGER,
        primaryKey: true,
        allowNull: false
    },
    value: {
        type: Sequelize.INTEGER,
        allowNull: false,
    },
    plan_id: {
        type: Sequelize.STRING,
        allowNull: false
    },
    quota_id: {
        type: Sequelize.STRING,
        allowNull: false
    }

}, {
    sequelize,
    modelName: 'plan_quota',
    tableName: 'plans_planquota'
});

The PlanQuota table has link to the UserPlan using plan_id and Quota table using quota_id.

User.findOne({
   where: {
      'id': user_id
   },
   include: [
       {
           model: UserPlan,
           include: [
              {
                  model: PlanQuota
              }
           ]
       }
   ]
 }).then(d => {
     console.log('d: ', d);
 });

The UserPlan is associated with User model, and I'm able to include the UserPlan using User.

But how Can I include PlanQuota and the Quota using the join?

sequelize.js node.js
1 Answers

I think you need to "include" the associated model, not the junction table:

User.findOne({
   where: {
      'id': user_id
   },
   include: [
       {
           model: UserPlan,
           include: [
              {
                  model: Qouta//This is the change
              }
           ]
       }
   ]
 }).then(d => {
     console.log('d: ', d);
 });

Do you have your associations setup? like:

UserPlan.belongsToMany(Qouta, { through: PlanQuota  });
Qouta.belongsToMany(UserPlan, { through: PlanQuota  });
user_83408890
Related