文档-官方
文档-github
文档-常用操作
sequelize 会自动给define的表名加复数 s，如果不想要，可以设置 freezeTableName: true 链接
数据类型
sequelize中model的数据类型对应mysql数据类型
用法
排序默认升序
RuleRefidsModel.findOne({
where: {
art_id: item.art_id,
ref_id: item.ref_id, // undefined 会被 sequalize 转成 NULL 去查询
rule_id: item.id, // 不是唯一索引
},
attributes: ['id'],
raw: true
})
async get(sid) {
let {sequelize} = this;
this.sql = `select * from ${this.session_table} where session_key='${sid}'`;
try {
let result = (await sequelize.query(this.sql, {type: Sequelize.QueryTypes.SELECT}))[0];
return result || {};
} catch (e) {
throw e;
}
}
# 另一个
let sql_count = `select count(id) count from auth_user`;
sequelize.query(sql_count, {type: sequelize.QueryTypes.SELECT}).then(result => {
let count = result[0].count + config.REDIS.KEYS.MARKET_USER_COUNT_ADD;
redis.client.set(config.REDIS.KEYS.MARKET_USER_COUNT, count, 'EX', config.REDIS.EXPIRE.MARKET_USER_COUNT);
return res.fmt({data: {count: count}})
}).catch(err => {
next(err)
});
定义
# 唯一索引
const Model = app.model.define('activity_enroll', {
id: {
type: DataTypes.INTEGER(11),
allowNull: false,
primaryKey: true,
autoIncrement: true
},
enroll_id: {
type: DataTypes.INTEGER(11),
defaultValue: 1,
},
}, {
tableName: 'activity_enroll',
freezeTableName: true,
'indexes': [
{
unique: true,
fields: ['enroll_id', 'unionid']
}
]
});
# 对时间的处理
var VippushTask = sequelize.define('wepush_vippush_tasks', {
media_id: {
type: Sequelize.STRING,
allowNull: false,
comment: '本次任务用的素材'
},
rules: {
type: Sequelize.TEXT,
comment: '发送给大数据的规则的序列化结果'
},
redis_status: {
type: Sequelize.INTEGER,
allowNull: false,
defaultValue: 1,
comment: '大数据往redis塞人的进度：0完成 1未完成，默认1，完成后大数据会向redis publish消息，收到消息后，更新为0'
},
push_status: {
type: Sequelize.INTEGER,
allowNull: false,
defaultValue: 1,
comment: '推送状态：0完成 1未完成，默认1，redis_status为0并且 取不出来人 之后，更新为0'
},
isCanceled: {
type: Sequelize.INTEGER,
allowNull: false,
defaultValue: 0,
comment: '运营取消发送：0未取消 1取消了，默认0'
},
msgtype: {
type: Sequelize.INTEGER,
defaultValue: 1,
comment: '素材类型：1图文；2图片'
},
}, {
'createdAt': false,
'updatedAt': false,
'freezeTableName': true,
'tableName': 'wepush_vippush_tasks',
'indexes': [
{
fields: ['redis_key']
}
]
})
使用
upsert
无则创建，有则更新。链接
WepushLogsModel.upsert(art, {where: {msg_id: art.MsgId, task_id: art.task_id}}, this.mysqlErrorHandle)
update
文档链接 返回值是数组，第一个值为影响到的行数。
Task.bulkCreate([
{subject: 'programming', status: 'executing'},
{subject: 'reading', status: 'executing'},
{subject: 'programming', status: 'finished'}
]).then(() => {
return Task.update(
{ status: 'inactive' }, /* set attributes' value */
{ where: { subject: 'programming' }} /* where criteria */
);
}).then(([affectedCount, affectedRows]) => {
// Notice that affectedRows will only be defined in dialects which support returning: true
// affectedCount will be 2
return Task.findAll();
}).then(tasks => {
console.log(tasks) // the 'programming' tasks will both have a status of 'inactive'
})
sequelize.literal
链接
在 sql 语句中存在这样的写法 UPDATE tablea SET a = a + b;,
tablea.update(
{
a: sequelize.literal('a + b')
}
)
where
itbilu.com/nodejs/npm/… 应该用Op，新版本用 '$lte'啥的会报错了
# /Users/liujunyang/work/xuetang/pay/server/controllers/pay/pay_notify/pay.notify.js:317
order_all_update_where = {
user_id: order_third.user_id,
order_no,
order_state: {'$in': [0, -1]},
_v: order_all._v
};
Model.findAll({
where: {
attr1: {
$gt: 50
},
attr2: {
$lte: 45
},
attr3: {
$in: [1,2,3]
},
attr4: {
$ne: 5
}
}
})
// WHERE attr1 > 50 AND attr2 <= 45 AND attr3 IN (1,2,3) AND attr4 != 5
# like
# /Users/liujunyang/work/ljy/fundcms/server/controllers/cms/organization.js:166
let salesmanList = await SalesmanModel.findAll({
where: {
org_number: {
[Op.like]: `${body.org.org_number}%`,
},
is_deleted: 0,
},
raw: true,
})
删除
# /Users/liujunyang/work/ljy/fundcms/server/controllers/cms/salesman.js
await SalesmanModel.destroy({
where: {
id,
}
})
分页
# /Users/liujunyang/work/ljy/fundcms/server/controllers/cms/sale.js
const {Op, fn, col} = require('../../helpers/mysql').Sequelize;
const SaleModel = require('../../models').SaleModel
Promise.all([
SaleModel.findAll({
where,
attributes: [
[fn('COUNT', col('id')), 'count'],
[fn('SUM', col('capital')), 'capital_all'],
],
}),
SaleModel.findAll({
where,
order: [
['investment_time', 'DESC'],
],
offset: (pageNo - 1)*pageSize,
limit: pageSize,
})
]).then(result => {
return res.fmt({
data: result
})
}).catch(err => {
console.log('sale err', err)
return next(formatDbError(err))
})
分组 groupby
# /Users/liujunyang/work/ljy/fundcms/server/controllers/cms/report.js
Promise.all([
// 基金全部销量，按基金分组
SaleModel.findAll({
where,
group: 'product_id',
attributes: [
'product_id',
[fn('SUM', col('capital')), 'capital_all'],
]
}),
// 基金兑付量，按基金分组
SaleModel.findAll({
where: Object.assign({}, where, {ispayed: 1}),
group: 'product_id',
attributes: [
'product_id',
[fn('SUM', col('capital')), 'capital_all'],
]
}),
// 基金销量，按出单人分组
SaleModel.findAll({
where,
group: 'salesman_id',
attributes: [
'salesman_id',
[fn('SUM', col('capital')), 'capital_all'],
]
})
]).then(result => {
return res.fmt({
data: result
})
}).catch(err => {
console.log('err', err)
return next(formatDbError(err))
})
事务
# pay/server/controllers/order/create.js:227
await sequelize.transaction((t) => {
let funcs = [
// 使用优惠券
Promise.all(using_coupon_infos.filter(item => item.amount_used != 0).map(item => {
return UserCouponModel.update({
is_used: 1,
use_time: Date.now(),
amount_used: item.amount_used
}, {where: {id: item.id}, transaction: t});
})),
//创建订单
OrderAllModel.create(doc_order_all, {transaction: t}),
//创建订单商品关系
OrderGoodsModel.bulkCreate(doc_order_goods, {transaction: t})
];
if (doc_order_third) funcs.push(OrderThirdModel.create(doc_order_third, {transaction: t}));
return Promise.all(funcs);
}).then((result) => {
this.res_order = result[1];
this.res_order_goods = result[2];
this.res_order = this.mergeOrderGoods(this.res_order, this.res_order_goods);
if (result[3]) { // 有result[3],表示有0元订单直接支付成功，要通知调用方支付成功
new OrderNotify({type: 'order'}).notifyClient({
redirect_url: this.res_order.redirect_url,
user_id: this.res_order.user_id,
order_no: this.res_order.order_no,
req_id: this.req.req_id
});
} else {
//没有0元订单的话要到有效期的时候关闭订单（延时任务）
orderExpireTask.addDelayJob({order_no: this.res_order.order_no, expire_time: this.expire_time, req_id: this.req.req_id}, this.expire_time);
}
return this.end({data: this.res_order});
})
};
跟egg合用
fs
#获取用户的userid
async userid (){
let str = `select user_id from auth_userprofile where weixin_unionid="${this.ctx.user.unionid}"`
let res = await this.app.model.query(str, {type:'SELECT'})
return this.ctx.body = res[0]
}
