mysql - Sequelize 连接表不返回附加字段

Question

我正在测试 Sequelize，到目前为止我很喜欢它。现在我遇到了一个无法解决的问题，并且在网上找不到任何答案。

所以我有这三个通过迁移创建的表

用户:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Users', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      .
      . // All user data here
      .
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Users');
  }
};

属性:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('Properties', {
      id: {
        allowNull: false,
        autoIncrement: true,
        primaryKey: true,
        type: Sequelize.INTEGER
      },
      .
      . // All property data here
      .
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('Properties');
  }
};

和 PropertyUser:

'use strict';
module.exports = {
  up: (queryInterface, Sequelize) => {
    return queryInterface.createTable('PropertyUsers', {
      property_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        references: {
          model: 'Properties',
          key: 'id'
        }
      },
      user_id: {
        type: Sequelize.INTEGER,
        allowNull: false,
        primaryKey: true,
        references: {
          model: 'Users',
          key: 'id'
        }
      },
      role: {
        type: Sequelize.ENUM('admin', 'basic', 'read'),
        allowNull: false,
        defaultValue: 'read'
      },
      createdAt: {
        allowNull: false,
        type: Sequelize.DATE
      },
      updatedAt: {
        allowNull: false,
        type: Sequelize.DATE
      }
    });
  },
  down: (queryInterface, Sequelize) => {
    return queryInterface.dropTable('PropertyUsers');
  }
};

因此，我们有通过 PropertyUser 表连接的用户和属性表，PropertyUser 表保存有关用户在属性中拥有的角色的信息(管理员、基本、读取)。

我也创建了这些模型

用户:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const User = sequelize.define('User', {
    firstname: DataTypes.STRING,
    .
    .
    .
  }, {});
  User.associate = function(models) {
    // associations can be defined here
    User.belongsToMany(models.Property, {through: 'PropertyUsers', foreignKey: 'user_id', as: 'properties'})
  };
  return User;
};

属性:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const Property = sequelize.define('Property', {
    .
    .
    .
  }, {});
  Property.associate = function(models) {
    // associations can be defined here
    Property.belongsToMany(models.User, {through: 'PropertyUsers', foreignKey: 'property_id', as: 'users'})
  };
  return Property;
};

属性用户:

'use strict';
module.exports = (sequelize, DataTypes) => {
  const PropertyUser = sequelize.define('PropertyUser', {
    property_id: DataTypes.INTEGER,
    user_id: DataTypes.INTEGER,
    role: DataTypes.ENUM('admin', 'basic', 'read'),
  }, {});
  PropertyUser.associate = function(models) {
    // associations can be defined here
    PropertyUser.belongsTo(models.Property, {foreignKey: 'property_id'});
    PropertyUser.belongsTo(models.User, {foreignKey: 'user_id'});
  };
  PropertyUser.removeAttribute('id');
  return PropertyUser;
};

现在，当我尝试获取带有 ID 的属性并包含属于该属性的用户时，它确实会返回每个用户的联接表数据，但联接表数据仅包括 user_id、property_id、createdAt 和 UpdatedAt 而不是角色。

这是我用来获取数据的代码。

return new Promise(async(resolve, reject) => {
        try {
            const result = await models.Property.findOne({
                where: {
                    id: propertyId
                },
                include: [{
                    model: models.User,
                    as: 'users',
                }]
            });
            resolve(result);
        } catch (e) {
            reject(e);
        }
    });

我做错了什么？已经感谢您的帮助!

编辑:看起来，当 Sequelize 构造 SQL 语句时，它并没有将角色添加到语句中。这是从 PropertyUser 表获取数据的语句部分

`users->PropertyUsers`.`createdAt` AS `users.PropertyUsers.createdAt`, `users->PropertyUsers`.`updatedAt` AS `users.PropertyUsers.updatedAt`, `users->PropertyUsers`.`property_id` AS `users.PropertyUsers.property_id`, `users->PropertyUsers`.`user_id` AS `users.PropertyUsers.user_id`

Answer 1

经过几个小时的尝试不同的事情后，我找到了问题所在。我不知道为什么它不能按照我原来的帖子中的实现方式工作，但这里是更改。

在用户模型中我改变了

User.belongsToMany(models.Property, {through: 'PropertyUsers', foreignKey: 'user_id', as: 'properties'})

到(注意 through 的变化)

User.belongsToMany(models.Property, {through: models.PropertyUser, foreignKey: 'user_id', as: 'properties'})

在属性模型中也是如此，我将 through: 'PropertyUsers' 更改为 through: models.PropertyUser