nodejs的orm模块sequelize功能很强大也很深奥,记录下今天使用hooks 及 association遇到了几个坑。
hooks:顾名思义就是钩子,简单的理解为可以定义相关动作后的关联动作
association: 关联,显式的指定该模型的关联关系。个人觉得 ORM 最大的问题就是关联查询及其复杂,反人类简直。有了 association 就可以显式的指定关联,方便查询。
-
定义modle
在定义modle的时候还比较容易,只需要在正常的定义后面加上 hooks 和 association 部分,举个例子
const T= app.model.define(
't',
{
id: { type: STRING(64), primaryKey: true, allowNull: false },
created_at: {
type: DATE,
get() {
return moment(this.getDataValue('created_at')).format('YYYY-MM-DD HH:mm:ss');
}
},
updated_at: {
type: DATE,
get() {
return moment(this.getDataValue('updated_at')).format('YYYY-MM-DD HH:mm:ss');
}
},
deleted_at: { type: DATE },
deleted: { type: BOOLEAN, allowNull: false, defaultValue: false },
created_by: { type: STRING(64), allowNull: false },
updated_by: { type: STRING(64), allowNull: false },
name: { type: STRING(64), allowNull: false },
company_id: { type: STRING(64), allowNull: false }
},
{
timestamps: true,
paranoid: true,
createdAt: 'created_at',
updatedAt: 'updated_at',
deletedAt: 'deleted_at',
hooks: {
afterDestroy: function(t, options) {
t.update( { deleted: true }, options);
return null;
}
}
}
);
T.associate = function() {
app.model.T.belongsTo(app.model.Company, { as: 'company', foreignKey: 'company_id', targetKey: 'id' });
}
- 查询modle
let query = {
attributes: {
include: [
[Sequelize.col('company.name'), 'company_name']
],
exclude: [
'deleted_at',
'deleted',
'created_by',
'updated_by'
]
},
include: [
{
model: model.Company,
required: false,
as: 'company',
attributes: []
}
],
where: {
name: { [Op.substring]: name.trim() }
},
order: [
['updated_at', 'DESC'],
['name', 'ASC']
],
limit: parseInt(limit) || 10,
offset: parseInt(offset) || 0
};
let index = await model.T.findAndCountAll(query);