在原有的基础字段上数值+1 可以批量修改
async getWeibaoList() {
const {ctx,app}=this;
//条件查询
let Op = app.Sequelize.Op;
const literal = app.Sequelize.literal;
let i=1
let res=await app.model.Weibao.update(
{
shijian: literal(`shijian + ${i}`),
// readCount : literal("read_count + 1"), // 这里有个坑注意大小写
},
{
where: {
id:{
[Op.in]: [49,50,51],
}
},
}
);
ctx.apiSuccess(res)
}
聚合查询求和
async sumlion(){
const {ctx,app}=this;
const { Sequelize } = app;
let Op = app.Sequelize.Op;
//投入数的汇总
let rows=await ctx.model.Lion.findAll({
where:{
time:{
[Op.lte]: new Date('2022-03-04 00:00:00'), //小于等于
[Op.gte]: new Date('2022-03-02 00:00:00') //大于等于
}
},
attributes:[[Sequelize.fn('SUM',Sequelize.col('Investment')), 'Investment_sum']]
})
ctx.apiSuccess(rows);
}
聚合查询分组统计
let res=await app.model.Friend.findAll({
where:sqlwhere,
attributes:[[Sequelize.fn('COUNT',Sequelize.col('id')), 'id_count'],'datatime'],
// 分组查询 不能排序
group: ['datatime']
})
批量修改
// 批量修改
let res= await app.model.Library.update({
lenduser,lendtime,state:'Y',lendreason
}, {
// 条件
where: {
code:{
[Op.in]: erweida,
},
state:'N'
}
});
批量更改
注意 updateOnDuplicate是在插入的时候如果主键冲突就执行更新操作
//有id相同就是修改 没有id相同就是创建 批量修改创建 ['id','toconfigure','inspection'] 需要修改创建的字段
let res=await app.model.Toconfigure.bulkCreate(arrAfter,{updateOnDuplicate:['id','toconfigure','inspection']});
ctx.apiSuccess(res);
批量创建
let arrAfter = []//数组对象
codedatas.forEach(item => {
item.warehousinguser=warehousinguser
arrAfter.push(item)
})
//批量创建
let b=await app.model.Library.bulkCreate(arrAfter);
批量删除
let res = await app.model.Image.destroy({
where:{
id:ids
}
})
ctx.apiSuccess(res )
创建
let row=await this.app.model.Image.create({
url
})
分页查询
async getMaterialsList(){
let { ctx, app } = this;
let Op = app.Sequelize.Op;
// 参数验证
ctx.validate({
page: { type: 'int', required: true, desc: '页码' },
limit: { type: 'int', required: false, desc: '页数' },
keyword: { type: 'string', required: false, desc: '关键字', range: { max: 30 } }
})
let page = ctx.params.page ? parseInt(ctx.params.page) : 1;
//这里params还是query看路由 改
let limit = ctx.query.limit ? parseInt(ctx.query.limit) : 10;
let keyword = ctx.query.keyword
let offset = (page - 1) * limit;
let sqlwhere = {
purpose: {
[Op.like]: '%' + keyword + '%'
}
}
if (!keyword) delete sqlwhere.purpose
let list = await app.model.Mater.findAndCountAll({
where:sqlwhere,
order: [
['created_time', 'DESC']
],
offset,
limit
})
ctx.apiSuccess(list);
}
mysql事务的提交 回滚
async handle() {
const { ctx, app } = this;
let current_user_id = ctx.authUser.id;
let id = parseInt(ctx.params.id)
// 参数验证
ctx.validate({
nickname: { type: 'string', required: false, desc: '昵称'},
status: { type: 'string', required: true, range: { in: ['refuse', 'agree', 'ignore'] }, desc: '处理结果' },
lookme: { type: 'int', required: true, range: { in: [0, 1] }, desc: '看我' },
lookhim: { type: 'int', range: { in: [0, 1] }, required: true, desc: '看他' },
});
// 查询该申请是否存在
let apply = await app.model.Apply.findOne({
where: {
id,
friend_id: current_user_id,
status: "pending"
},
include: [{
model: app.model.User
}]
});
if (!apply) ctx.throw(400, '该记录不存在');
let { status, nickname, lookme, lookhim } = ctx.request.body;
let transaction;
try {
// 开启事务
transaction = await app.model.transaction();
// 设置该申请状态
await apply.update({
status
}, { transaction });
// 同意的话,添加到好友列表中
if (status == 'agree') {
// 加入到对方好友列表
await app.model.Friend.create({
friend_id: current_user_id,
user_id: apply.user_id,
nickname: apply.nickname,
lookme: apply.lookme,
lookhim: apply.lookhim,
}, { transaction });
// 将对方加入到我的好友列表
await app.model.Friend.create({
friend_id: apply.user_id,
user_id: current_user_id,
nickname,
lookme,
lookhim,
}, { transaction });
}
// 提交事务
await transaction.commit();
// 消息推送
if (status == 'agree') {
let message = {
id: (new Date()).getTime(), // 唯一id,后端生成唯一id
from_avatar: ctx.authUser.avatar,// 发送者头像
from_name: apply.nickname || ctx.authUser.nickname || ctx.authUser.username, // 发送者昵称
from_id: current_user_id, // 发送者id
to_id: apply.user_id, // 接收人/群 id
to_name: nickname || apply.user.nickname || apply.user.username, // 接收人/群 名称
to_avatar: apply.user.avatar, // 接收人/群 头像
chat_type: 'user', // 接收类型
type: "system",// 消息类型
data: "你们已经是好友,可以开始聊天啦", // 消息内容
options: {}, // 其他参数
create_time: (new Date()).getTime(), // 创建时间
isremove: 0, // 是否撤回
}
ctx.sendAndSaveMessage(apply.user_id, { ...message });
message.from_avatar = apply.user.avatar;
message.from_name = nickname || apply.user.nickname || apply.user.username;
message.from_id = apply.user.id;
message.to_avatar = ctx.authUser.avatar;
message.to_name = apply.nickname || ctx.authUser.nickname || ctx.authUser.username;
message.to_id = current_user_id;
ctx.sendAndSaveMessage(current_user_id, { ...message });
}
return ctx.apiSuccess('操作成功');
} catch (e) {
// 事务回滚
await transaction.rollback();
return ctx.apiFail('操作失败');
}
}
多个字段模糊查询 或空查询
//多个字段模糊查询 或空查询
let sqlwhere = {
[Op.or]: [
{username: {[Op.like]: `%${search || ''}%`}},
{nickname: {[Op.like]: `%${search || ''}%`}},
],
}
let list = await app.model.Register.findAndCountAll({
where: sqlwhere,
order: [
['created_time', 'DESC']
],
offset,
limit
})
ctx.apiSuccess({
list,roles
});