node.js - 对没有关系的关系数据库使用急切加载sequelize

Question

我有一个关系数据库(Postgres)，没有显式关系，即外键。但我正在使用 Sequelize 来查询数据库中的数据，并且我想通过急切加载 Sequelize 来查询数据。

模型

供应商

const Supplier = sequelize.define(
    'Supplier',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      deleted: {
        type: DataTypes.BOOLEAN,
        defaultValue: false,
        field: 'excluido',
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
        field: 'datacriacao',
      },
      updatedAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
        field: 'dataalteracao',
      },
      socialName: {
        type: DataTypes.STRING,
        allowNull: false,
        field: 'razaosocial',
      },
      logo: {
        type: DataTypes.STRING,
        allowNull: true,
        field: 'logo',
      },
    },
    {
      underscored: true,
      freezeTableName: true,
      timestamps: false,
      tableName: 'fornecedor',
    },
  );

供应商地址:

const SupplierAddress = sequelize.define(
    'SupplierAddress',
    {
      id: {
        type: DataTypes.INTEGER,
        primaryKey: true,
        autoIncrement: true,
      },
      createdAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
        field: 'datacriacao',
      },
      updatedAt: {
        type: DataTypes.DATE,
        defaultValue: DataTypes.NOW,
        field: 'dataalteracao',
      },
      address: {
        type: DataTypes.STRING,
        allowNull: false,
        field: 'endereco',
      },
      supplierId: {
        type: DataTypes.INTEGER,
        allowNull: false,
        field: 'idfornecedor',
      },
    },
    {
      underscored: true,
      freezeTableName: true,
      timestamps: false,
      tableName: 'enderecofornecedor',
    },
  );

我期望可以通过以下方式获得结果:

SELECT * FROM supplier AS s INNER JOIN supplierAddress AS sa ON s.id = sa.supplierId;

如何使用急切加载来进行此咨询？我是 Sequelize 的新手。

我尝试过类似的方法，但不起作用:

static async getSuppliers() {
    try {
      database.Supplier.hasMany(database.SupplierAddress, {
        foreignKey: 'supplierId'
      });
      database.SupplierAddress.belongsTo(database.Supplier);
      const result = await database.Supplier.findAll({ include: database.SupplierAddress});
      console.log(JSON.stringify(result, null, 2));

      const list = await Promise.all(result);
      return list;
    } catch (error) {
      error.location =
        ' Exception raised by getSuppliersin SupplierService. ';
      throw error;
    }
  }

Answer 1

const supplierModels = await database.Supplier.findAll({
  include: [{
    model: database.SupplierAddress,
    // this option is for INNER JOIN
    required: true
  }
});
const plainSupplierObjects = supplierModels.map(x => x.get({ plain: true })
console.log(JSON.stringify(plainSupplierObjects, null, 2));

// you don't need this line because you already got all Supplier records asynchronously.
//const list = await Promise.all(result);
return plainSupplierObjects;

此外，您不需要每次在获得结果之前都定义关联。只需在 Sequelize 中注册模型后立即执行此操作一次。