egg-sequelize创建 修改 删除 查询

在原有的基础字段上数值+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
    });