我有一个关系数据库(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;
如何使用急切加载来执行此咨询?我是续集的新手。
我尝试了这样的事情,但没有工作:
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;
}
}
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;
此外,在获得结果之前,您不需要每次都定义关联。只需在续集中注册您的模型后立即执行此操作一次即可。
不必在每次使用模型时都定义关系,必须在定义两者后关联模型。一个常见的解决方案是使用静态方法associate
接收所有模型并在那里进行关联。
无论如何,如果您的目标不是约束关系,并且仅将其保留在逻辑层上,则可以使用关联定义[0]中的选项constraints
,例如:
database.SupplierAddress.belongsTo(database.Supplier, {
as: 'Suppliers',
foreignKey: 'supplierId',
constraints: false,
});
添加:
要查找结果,在这种情况下,您可以这样做:
database.SupplierAddress.findAll({
include: [{
model: database.Supplier,
as: 'Suppliers',
}]
});
但我建议创建一个新的范围[1]:
database.SupplierAddress.addScope('withsuppliers', {
include: [{
models: database.Supplier,
as: 'Suppliers',
}],
});
然后使用:
database.SupplierAddress.scope('withsuppliers').findAll();
0:https://sequelize.org/master/manual/constraints-and-circularities.html 1: https://sequelize.org/master/manual/scopes.html