我正在尝试根据UUID(我也有ID)使两个表联接,这些表存在一些复杂的关系……
当我运行查询时,出现了错误。
第一个表名为users,它的UUID称为registry_uuid。 第二个表是beneficiaries,但这个表有两个UUID,分别是uuid_beneficiary和uuid_benefactor。
为什么?因为第一个表有一个列user_type_id,通过此列我们可以知道它是用户beneficiary还是benefactor。 而第二个表则是用于确定哪些用户存在关联关系。
用户模型:
const User = sequelize.define('users', {
registry_uuid: {
type: Sequelize.UUIDV4,
defaultValue: Sequelize.UUIDV4,
allowNull: false
},
user_type_id: {
type: Sequelize.INTEGER,
defaultValue: 1,
allowNull: false
}
}
受益人模型:
const Beneficiary = sequelize.define('beneficiaries', {
uuid_benefactor: {
type: Sequelize.UUIDV4,
defaultValue: Sequelize.UUIDV4,
allowNull: false
},
uuid_beneficiary: {
type: Sequelize.STRING,
defaultValue: Sequelize.UUIDV4,
allowNull: false
},
created_at: {
type: Sequelize.DATE,
defaultValue: Sequelize.NOW
},
disassociation_date: {
type: Sequelize.DATE,
defaultValue: null
}
}
查询:
async function getBenefactorOfBeneficiary (benefactorUuid, arrayAttributes) {
arrayAttributes = arrayAttributes || ['registry_uuid', 'name', 'last_name', 'picture']
return UserModel.findOne({
where: {
registry_uuid: {
[Op.eq]: benefactorUuid
}
},
include: [{
model: BeneficiaryModel,
}],
attributes: arrayAttributes,
raw: true
})
}
关系:
UserModel.hasMany(beneficiaryModel, {foreignKey: 'uuid_benefactor'})
beneficiaryModel.belongsTo(UserModel, {foreignKey: 'registry_uuid'})
我希望您能够输出以下内容:
示例:
{
"name": "string",
"lastName": "string",
"picture": "string",
"created_at" "string"
}
显然我在控制器中修改了响应