引言

在第4小节上面，已经用了的 findAndCountAll() 分页查询方法，本小结重点围绕排序、分组等细节部分讲讲

排序

普通排序

在我们直接代码的基础上，增加排序，比如根据年龄排序

let {rows, count} = await userDB.findAndCountAll({
  offset: offset,
  limit: pageSize,
  where: where,
  order: [['age', 'desc'], ['sex', 'asc']]
})

加入order参数，增加age倒序查询

高阶排序

需求，根据匹配程度排序返回，比如搜索name，或者描述中含有指定keyword的，按匹配度返回

（1）user表增加字段description

记得去db/model/user.js里面增加字段

（2）增加search接口

/**
 * 
 * @api {get} /api/user/search 用户搜索
 * @apiName 用户搜索
 * @apiGroup 用户
 * @apiDescription 返回搜索用户数据
 * @apiVersion  1.0.0
 * 
 * @apiParam {String} keyword='' 
 * 
 * @apiParamExample  {type} Request-Example:
 * {
 *     keyword: 1
 * }
 * 
 * @apiSuccess {Number} code 200
 * @apiSuccess {Array} resultList 数据集合
 * @apiSuccess {Object} paging 分页信息
 * @apiSuccessExample {type} Response-Example:
 * {
 *  code: 200,
 *  resultList: [],
 *  paging: {
 *    pageNo: 1,
 *    pageSize: 10,
 *    totalCount: 12,
 *    totalPage: 2
 *  }
 * }
 * 
 */
 router.get('/search', async (req, res) => {
  const params = req.body;

  let {pageNo=1, pageSize=10} = params
  pageSize = pageSize > 100 ? 100 : pageSize
  let offset = (pageNo - 1) * pageSize

  pageNo = +pageNo
  pageSize = +pageSize

  let keyword = params.keyword

  let {rows, count} = await userDB.findAndCountAll({
    raw: true,
    offset: offset,
    limit: pageSize,
    order: [ Sequelize.literal(`CASE
      WHEN name = "${keyword}" THEN 1 
      WHEN name like "${keyword}%" THEN 2
      WHEN name like "%${keyword}" THEN 3
      WHEN name like "%${keyword}%" THEN 4
      WHEN description like "${keyword}%" THEN 5
      WHEN description like "%${keyword}" THEN 6
      WHEN description like "%${keyword}%" THEN 7
      END
    `)],
    where: {
      [Op.or]: {
        name: {
          [Op.like]: `%${keyword}%`
        },
        description: {
          [Op.like]: `%${keyword}%`
        }
      }
    }
  })

  res.send({
    code: 200,
    resultList: rows,
    paging: {
      pageNo: pageNo,
      pageSize: pageSize,
      totalCount: count,
      totalPage: Math.ceil(count / pageSize)
    }
  })
})

先执行看看结果：