为什么Sequelize认为我的模型没有关联？-Java 学习之路

我正在尝试制作一个Sequelize查询，但它一直告诉我我的模型没有关联 . 然而我确实宣布了一个协会...我想我根本不理解协会以及我认为......

我做了一个你可以克隆的回购来自己看问题：https://github.com/WaltzApp/sequelize-troubleshooting

我认为我遇到这个问题的原因是Sequelize不支持复合外键，所以我必须在我的连接中使用自定义 on 子句，但它在关联本身中指定了相同的连接方法 . 这可以解释为什么我有时会得到Sequelize打印出的功能完善的SQL，但查询仍然失败（可能是因为虽然数据库查询成功，但将结果转换为Sequelize entiteis失败） .

此复制工作的先前版本也在下面复制：

Relevant Models

usersRoles：

module.exports = (sequelize, DataTypes) => {
  const usersRoles = sequelize.define('usersRoles',
    {
      key: {
        type: DataTypes.UUID,
        primary: true,
      },
      userUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      roleUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      oemUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      propertyManagerUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      tenantUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      [MORE FIELDS IRRELEVANT TO MY PROBLEM]
    },
    {
      tableName: 'usersRoles',
      indexes: [
        {
          name: 'userRoleOem',
          fields: ['userUid', 'roleUid', 'oemUid'],
          unique: true,
          where: {
            oemUid: { $ne: null },
            propertyManagerUid: null,
            tenantUid: null,
          },
        },
        {
          name: 'userRolePropertyManager',
          fields: ['userUid', 'roleUid', 'propertyManagerUid'],
          unique: true,
          where: {
            oemUid: null,
            propertyManagerUid: { $ne: null },
            tenantUid: null,
          },
        },
        {
          name: 'userRoleTenant',
          fields: ['userUid', 'roleUid', 'tenantUid'],
          unique: true,
          where: {
            oemUid: null,
            propertyManagerUid: null,
            tenantUid: { $ne: null },
          },
        },
        {
          name: 'compositePK',
          fields: ['userUid', 'roleUid', 'oemUid', 'propertyManagerUid', 'tenantUid'],
          unique: true,
        },
      ],
      freezeTableName: true,
      timestamps: false,
    });

  usersRoles.removeAttribute('id');

  usersRoles.associate = (models) => {
    models.usersRoles.belongsTo(models.oems, { foreignKey: 'oemUid' });
    models.usersRoles.belongsTo(models.propertyManagers, { foreignKey: 'propertyManagerUid' });
    models.usersRoles.belongsTo(models.tenants, { foreignKey: 'tenantUid' });
    models.usersRoles.belongsTo(models.users, { foreignKey: 'userUid' });
    models.usersRoles.belongsTo(models.roles, { foreignKey: 'roleUid' });
    models.usersRoles.belongsTo(models.invitations, { as: 'invitation', foreignKey: 'compositePK' });
  };

  return usersRoles;
};

邀请：

module.exports = (sequelize, DataTypes) => {
  const invitations = sequelize.define('invitations',
    {
      uid: {
        type: DataTypes.UUID,
        primaryKey: true,
      },
      guestUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      mappingGroupUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      invitedByUid: {
        type: DataTypes.UUID,
        allowNull: false,
      },
      adminUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      propertyManagerUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      tenantUid: {
        type: DataTypes.UUID,
        allowNull: true,
      },
      [MORE FIELDS IRRELEVANT TO MY PROBLEM]
    }, {
      tableName: 'invitations',
      freezeTableName: true,
      timestamps: false,
    });

  invitations.associate = (models) => {
    models.invitations.belongsTo(models.propertyManagers, { foreignKey: 'propertyManagerUid' });
    models.invitations.belongsTo(models.tenants, { foreignKey: 'tenantUid' });
    models.invitations.belongsTo(models.mappingGroups, { foreignKey: 'mappingGroupUid' });
    models.invitations.belongsTo(models.users, { as: 'guest', foreignKey: 'guestUid' });
    models.invitations.belongsTo(models.users, { as: 'invitedBy', foreignKey: 'invitedByUid' });
    models.invitations.belongsTo(models.users, { as: 'admin', foreignKey: 'adminUid' });
    models.invitations.hasMany(models.usersRoles, { as: 'invitation', foreignKey: 'compositePK' });
  };

  return invitations;
};

请注意，我将 invitations 和 usersRoles 模型与 models.invitations.hasMany(models.usersRoles, { foreignKey: 'compositePK' }) 行相关联 .

The Query

const path = require('path');
const glob = require('glob');
const Promise = require('bluebird');

const Sequelize = require('sequelize');
const configs = require('./test/_helpers/getConfigs');

const models = {};

const performQuery = (db) => {
  const { usersRoles, invitations } = db; // eslint-disable-line max-len

  const sql = {
    include: {
      model: invitations,
      as: 'invitation',
      on: {
        user: Sequelize.where(
          Sequelize.col('usersRoles.userUid'),
          '=',
          Sequelize.col('invitation.guestUid')),
        propertyManager: Sequelize.where(
          Sequelize.col('usersRoles.propertyManagerUid'),
          '=',
          Sequelize.col('invitation.propertyManagerUid')),
        tenant: Sequelize.where(
          Sequelize.col('usersRoles.tenantUid'),
          '=',
          Sequelize.col('invitation.tenantUid')),
      },
    },
  };
  return usersRoles.findAll(sql);
};

const sequelize = new Sequelize(
  configs.get('DB_NAME'),
  configs.get('DB_USER'),
  configs.get('DB_PSSWD'),
  {
    dialect: configs.get('DB_TYPE'),
    host: configs.get('DB_HOST'),
    port: configs.get('DB_PORT'),
  });

const modelsPath = path.join(__dirname, 'sequelize/models');
const globOpts = {
  cwd: modelsPath,
  ignore: '**/index.js',
  realPath: false,
  nosort: true,
  nodir: true,
};

glob('**/*.js', globOpts, (err, files) => {
  Promise.map(files, (file) => {
    const model = sequelize.import(path.join(modelsPath, file));
    models[model.name] = model;
  })
    .then(() => {
      Object.keys(models).forEach((modelName) => {
        if (models[modelName].associate) {
          models[modelName].associate(models);
        }
      });
      return performQuery(models);
    })
    .then((res) => {
      console.log('SUCCESS', res);
    }, (reason) => {
      console.error('FAILED', reason);
    });
});

The Output

FAILED { SequelizeEagerLoadingError: invitations is not associated to usersRoles!
    at Function._getIncludedAssociation (.../node_modules/sequelize/lib/model.js:573:13)
    at Function._validateIncludedElement (.../node_modules/sequelize/lib/model.js:489:53)
    at options.include.options.include.map.include (.../node_modules/sequelize/lib/model.js:385:37)
    at Array.map (native)
    at Function._validateIncludedElements (.../node_modules/sequelize/lib/model.js:381:39)
    at Promise.try.then.then (.../node_modules/sequelize/lib/model.js:1525:14)
    at tryCatcher (.../node_modules/bluebird/js/release/util.js:16:23)
    at Promise._settlePromiseFromHandler (.../node_modules/bluebird/js/release/promise.js:512:31)
    at Promise._settlePromise (.../node_modules/bluebird/js/release/promise.js:569:18)
    at Promise._settlePromise0 (.../node_modules/bluebird/js/release/promise.js:614:10)
    at Promise._settlePromises (.../node_modules/bluebird/js/release/promise.js:693:18)
    at Async._drainQueue (.../node_modules/bluebird/js/release/async.js:133:16)
    at Async._drainQueues (.../node_modules/bluebird/js/release/async.js:143:10)
    at Immediate.Async.drainQueues (.../node_modules/bluebird/js/release/async.js:17:14)
    at runCallback (timers.js:637:20)
    at tryOnImmediate (timers.js:610:5)
    at processImmediate [as _immediateCallback] (timers.js:582:5) name: 'SequelizeEagerLoadingError' }

Further attempt 我尝试将以下行添加到邀请模型中：

models.usersRoles.belongsTo(models.invitations);

在这种情况下，Sequelize会打印一些SQL，这些SQL在执行时完全按预期工作，但显然它不起作用，而是说 SequelizeDatabaseError: relation "usersRoles" does not exist .

执行（默认）：SELECT“usersRoles” . “key”，“usersRoles” . “userUid”，“usersRoles” . “roleUid”，“usersRoles” . “oemUid”，“usersRoles” . “propertyManagerUid”，“usersRoles” . “doorSchedule”，“usersRoles” . “cardId”，“usersRoles” . “notifyBy”，“usersRoles” . “contactEmail”，“usersRoles” . “tenantUid”，“usersRoles” . “createdAt”，“usersRoles” . compositePK“，”usersRoles“ . ”invitationUid“，”邀请“ . ”uid“AS”invitation.uid“，”邀请“ . ”状态“AS”invitation.status“，”邀请“ . ”guestUid“AS”邀请 . guestUid“，”邀请“ . ”firstName“AS”invitation.firstName“，”邀请“ . ”lastName“AS”invitation.lastName“，”邀请“ . ”email“AS”invitation.email“，”邀请“ . ”移动“AS”invitation.mobile“，”邀请“ . ”mappingGroupUid“AS”invitation.mappingGroupUid“，”邀请“ . ”doorSchedule“AS”invitation.doorSchedule“，”邀请“ . ”inviteByUid“AS”invitation.invitedByUid“ ，“邀请” . “adminUid”AS“invitation.adminUid”，“邀请” . “acceptedAt”AS“invitation.acceptedAt”，“邀请” . “re jectedAt“AS”invitation.rejectedAt“，”邀请“ . ”propertyManagerUid“AS”invitation.propertyManagerUid“，”邀请“ . ”tenantUid“AS”invitation.tenantUid“，”邀请“ . ”contactEmail“AS”invitation.contactEmail“ ，“邀请” . “notifyBy”AS“invitation.notifyBy”，“邀请” . “createdAt”AS“invitation.createdAt”，“邀请” . “updatedAt”AS“invitation.updatedAt”FROM“usersRoles”AS“usersRoles” LEFT OUTER JOIN“邀请”AS“邀请”ON“usersRoles” . “userUid”=“邀请” . “guestUid”和“usersRoles” . “propertyManagerUid”=“邀请” . “propertyManagerUid”和“usersRoles” . “tenantUid” . = “邀请”， “tenantUid”;查询失败{SequelizeDatabaseError：关系“usersRoles”不存在

1 回答

0
您永远不会调用 Model.associate() 方法，因此模型不会相互关联 . 诀窍在于关联可能需要尚未定义的模型，因此关联者必须是最后的 . 模型加载后循环遍历它们并调用 model.associate(models) 来创建关联 .

userRole 定义也不包含与 invitations 的关联 . 在Sequelize中，必须从要查询的一侧定义关系，或者如果您计划从两个对象查询关系，则必须同时定义关系 . 由于 invitations 有很多 userRoles ，我假设 userRoles 属于 invitations . 您在查询中将该表引用为 invitation ，因此您可能希望使用 as 属性对该关联进行别名 .

userRoles
```
models.usersRoles.belongsTo(models.invitations, {
  as: 'invitation', 
  foreignKey: 'compositePK',
});
```
回复于 2024-05-05T05:22:57+08:00

为什么Sequelize认为我的模型没有关联？

1 回答

相关问题