云开发-云函数实现联表查询和分页
发布于 4 年前 作者 pingcai 2567 次浏览 来自 分享

相关api

官方参考文档

Aggregate.lookup
Aggregate.limit
Aggregate.skip
Aggregate.count

实现原理

  • 基于 aggregate 操作集合
  • 使用 count 获取总数量和页数
  • 使用 limit 限制一次返回的数量
  • 使用 skip 实现发送下一页的数据
  • 使用 lookup 实现联表

示例

假设 orders 集合有以下数据

[
  {"_id":4,"book":"novel 1","price":30,"quantity":2},
  {"_id":5,"book":"science 1","price":20,"quantity":1},
  {"_id":6}
]

假设 books 集合有以下数据

[
  {"_id":"book1","author":"author 1","category":"novel","stock":10,"time":1564456048486,"title":"novel 1"},
  {"_id":"book3","author":"author 3","category":"science","stock":30,"title":"science 1"},
  {"_id":"book4","author":"author 3","category":"science","stock":40,"title":"science 2"},
  {"_id":"book2","author":"author 2","category":"novel","stock":20,"title":"novel 2"},
  {"_id":"book5","author":"author 4","category":"science","stock":50,"title":null},
  {"_id":"book6","author":"author 5","category":"novel","stock":"60"}
]

实现 ordersbooks 联表查询和分页的关键代码

const pageSize = 10	// 每页数据量,可以作为云函数的入参传入
const currPage = 1	// 查询的当前页数,可以作为云函数的入参传入
const db = cloud.database()
const $ = db.command.aggregate
// 定义联表实例
const aggregateInstance = db.collection('orders').aggregate()
  .lookup({
    from: 'books',
    localField: 'book',
    foreignField: 'title',
    as: 'bookList',
  })
const { totalCount } = await aggregateInstance.count('totalCount').end() 
// 计算总页数
const totalPage = totalCount === 0 ? 0 : totalCount <= pageSize ? 1 : parseInt(totalCount / pageSize) + 1
// 分页查询数据
const data = await aggregateInstance.replaceRoot({
    newRoot: $.mergeObjects([ $.arrayElemAt(['$bookList', 0]), '$$ROOT' ])
  })
  .project({
    bookList: 0
  })
  .limit(pageSize)
  .skip(currPage * pageSize)
  .end()
 return {currPage, pageSize, totalPage, totalCount, data}

预期输出结果

{
currPage: 1,
pageSize: 10,
totalPage: 1,
totalCount: 3,
data: [
  {
    "_id": 4,
    "title": "novel 1",
    "author": "author 1",
    "category": "novel",
    "stock": 10,
    "book": "novel 1",
    "price": 30,
    "quantity": 2
  },
  {
    "_id": 5,
    "category": "science",
    "title": "science 1",
    "author": "author 3",
    "stock": 30,
    "book": "science 1",
    "price": 20,
    "quantity": 1
  },
  {
    "_id": 6,
    "category": "science",
    "author": "author 4",
    "stock": 50,
    "title": null
  }]
}

如有错误,欢迎拍砖 ()

1 回复

分页存在一个bug,当  totalCount为 10、20、30…  时,会 多出来1页。应该为:let totalPage = totalCount === 0 ? 0 : totalCount <= pageSize ? 1 : Math.ceil(totalCount / pageSize) ;其中Math.ceil为向上求整。

回到顶部