Sequelize多对多关联查询报错：companies未关联到CompanySubscription

问题背景 #

现有companies、subscriptions和companySubscription三张表，需实现公司购买订阅的多对多关联（一个订阅可属于多个公司）。模型定义及数据插入操作均成功，但执行中间表关联查询时抛出错误：

"companies is not associated to CompanySubscription!"
中文翻译："companies未关联到CompanySubscription!"

错误原因 #

1. 关联关系不完整：仅定义了subscription到companies的单向belongsToMany关联，未补充companies到subscription的反向关联，导致关联逻辑不闭环。
2. 中间表未显式关联主表：多对多关联的中间表CompanySubscription默认不会自动与主表建立直接关联，必须显式定义belongsTo关联，才能在查询中间表时include主表数据。

修复步骤 #

1. 补充companies与subscription的双向多对多关联；
2. 在中间表CompanySubscription中定义与companies、subscription的belongsTo关联；
3. 确保模型加载顺序正确，避免循环引用问题（建议在统一初始化文件中加载所有模型并定义关联）。

修正后的代码 #

companies.js #

const sequelize = require("../utils/database");
const bcrypt = require("bcrypt");
const { DataTypes, Model } = require("sequelize");
const subscription = require("./subscriptions");
const CompanySubscription = require("./companySubscription");

class companies extends Model {}

companies.init(
  {
    id: {
      type: DataTypes.INTEGER,
      autoIncrement: true,
      primaryKey: true,
      allowNull: false,
    },
    name: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    email: {
      type: DataTypes.STRING,
      allowNull: false,
    },
    contactNo: {
      type: DataTypes.STRING,
      allowNull: true,
    },
    companySize: {
      type: DataTypes.INTEGER,
      allowNull: true,
    },
  },
  { sequelize, modelName: "companies" }
);

// 补充双向多对多关联
subscription.belongsToMany(companies, { through: CompanySubscription });
companies.belongsToMany(subscription, { through: CompanySubscription });

module.exports = companies;

companySubscription.js #

const sequelize = require("../utils/database");
const companies = require("./companies");
const subscription = require("./subscriptions");
const { DataTypes, Model } = require("sequelize");

class CompanySubscription extends Model {}

CompanySubscription.init(
  {
    id: {
      type: DataTypes.INTEGER,
      primaryKey: true,
      autoIncrement: true,
      allowNull: false,
    },
    status: {
      type: DataTypes.ENUM,
      values: ["active", "inactive"],
    },
    subscriptionType: {
      type: DataTypes.ENUM,
      values: ["Yearly", "Monthly"],
    },
    subscriptionPlanStartDate: {
      type: DataTypes.DATE,
    },
    subscriptionPlanEndDate: {
      type: DataTypes.DATE,
    },
    paidStatus: {
      type: DataTypes.ENUM,
      values: ["paid", "unpaid"],
    },
    paidDate: {
      type: DataTypes.DATE,
    },
  },
  { sequelize, modelName: "CompanySubscription" }
);

// 定义中间表与主表的关联（显式指定外键和别名，提升可读性）
CompanySubscription.belongsTo(companies, { 
  foreignKey: "companyId",
  as: "company"
});
CompanySubscription.belongsTo(subscription, { 
  foreignKey: "subscriptionId",
  as: "subscription"
});

module.exports = CompanySubscription;

修正后的查询代码 #

const billingList = async (req, res) => {
  const billingData = await CompanySubscription.findAll({
    include: [
      { model: companies, as: "company" },
      { model: subscription, as: "subscription" }
    ],
  });

  console.log("billing data ", billingData);
  res.json({ data: billingData });
};

验证说明 #

• 修正后，查询中间表CompanySubscription时可正常关联companies和subscription数据；
• 双向多对多关联支持从任意主表查询关联数据，例如：

  // 查询某公司的所有订阅
  const companySubscriptions = await companies.findByPk(companyId, {
    include: [{ model: subscription, through: CompanySubscription }]
  });
  

内容的提问来源于stack exchange，提问作者Jayna Tanawala