项目场景:
在node项目中使用sequelize进行关联查询关联查询
问题描述
sequelize 关联查询时,为关联表设置了别名,导致查询结果错误的问题。第一次请求正常,第二次开始就报错了。报错如下:
AssociationError [SequelizeAssociationError]: You have used the alias child in two separate associations. Aliased associations must have unique aliases.
原因分析:
await ctx.model.Category.hasMany(ctx.model.ChildCategory, {
foreignKey: "c_id",
as: 'children',
});
事实证明,问题是因为我在 Sequelize 上的初始设置时注册了两次模型。我一开始在model文件夹中已经注册过该模型了,然后我编写 Controller了才执行关联操作。在 sequelize 中,调用 hasMany 方法来建立关联并传递模型定义是一种注册模型的方式,实际上就是在注册 ChildCategory 模型。
模型:
// app/model/childCategory.js
module.exports = app => {
const DataTypes = app.Sequelize;
const Model = app.model.define('childCategory', {
child_id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
c_id: {
type: DataTypes.INTEGER(11),
allowNull: true
},
name: {
type: DataTypes.STRING(50),
allowNull: true
},
sort: {
type: DataTypes.INTEGER(11),
allowNull: true,
defaultValue: '0'
},
updatedAt: {
type: DataTypes.TIME,
allowNull: false,
defaultValue: '0000-00-00 00:00:00'
},
createdAt: {
type: DataTypes.TIME,
allowNull: false,
defaultValue: '0000-00-00 00:00:00'
}
}, {
tableName: 'childCategory'
});
Model.associate = function () {
}
return Model;
};
控制器:
// app/controller/category.js
async categoryfindAll() {
const { ctx } = this;
try {
await ctx.model.Category.hasMany(ctx.model.ChildCategory, {
foreignKey: "c_id",
as: 'children',
});
let res = await ctx.model.Category.findAll({
include: [
{
model: ctx.model.ChildCategory,
as: 'children',
required: false,
},
]
})
ctx.body = { code: 1, data: res }
} catch (e) {
console.log(e)
}
}
解决方案:
应该在初始化ChildCategory 模型就进行关联并设置别名
模型:
// app/model/childCategory.js
module.exports = app => {
const DataTypes = app.Sequelize;
const Model = app.model.define('childCategory', {
child_id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
c_id: {
type: DataTypes.INTEGER(11),
allowNull: true
},
name: {
type: DataTypes.STRING(50),
allowNull: true
},
sort: {
type: DataTypes.INTEGER(11),
allowNull: true,
defaultValue: '0'
},
updatedAt: {
type: DataTypes.TIME,
allowNull: false,
defaultValue: '0000-00-00 00:00:00'
},
createdAt: {
type: DataTypes.TIME,
allowNull: false,
defaultValue: '0000-00-00 00:00:00'
}
}, {
tableName: 'childCategory'
});
Model.associate = function () {
app.model.Category.hasMany(Model, {
foreignKey: "c_id", // 外键
as: 'children', // 别名
});
}
return Model;
};
控制器:
// app/controller/category.js
async categoryfindAll() {
const { ctx } = this;
try {
let res = await ctx.model.Category.findAll({
include: [
{
model: ctx.model.ChildCategory,
as: 'children',
required: false,
},
]
})
ctx.body = { code: 1, data: res }
} catch (e) {
console.log(e)
}
}