联表查询多个查询条件怎么实现？ - WXOPEN Club

我有两个集合：

集合1：users有以下数据

[{"userId":"zqiang","age":20},
{"userId":"lling","age":27},
{"userId":"wcjing","age":19},
{"userId":"wxia","age":21},
{"userId":"zying","age":18}]

集合2：course有以下数据

[{"name":"zqiang","subject":"体育","class":2,"position":"headman"},
{"name":"lling","subject":"英语","class":1,"position":"monitor"},
{"name":"wcjing","subject":"数学","class":2,"position":"headman"},
{"name":"lling","subject":"美术","class":3,"position":"student"},
{"name":"wcjing","subject":"英语","class":1,"position":"headman"},
{"name":"zqiang","subject":"英语","class":3,"position":"student"},
{"userId":"wxia","subject":"英语","class":2,"position":"headman"},
{"userId":"zying","subject":"英语","class":2,"position":"student"}]

要求：小程序端有4个筛选条件，分别是users集合的userId，course集合的subject，class，position字段，筛选数值等于数据库的数据。这4个筛选条件可能同时都会选择，也可能只有一项或两项，甚至有可能都没有。如果4个筛选条件都没有就返回所有的数据。

自己想过使用连表查询和动态拼接where条件的方法，试了1天也没有实现，望大神帮忙解决

1 回复

dshi1 楼5 年前

const db = cloud.database()
const _ = db.command
const $ = _.aggregate

let userMatch = { userId: _.neq('') }, // user默认条件， userId不为空
courseMatch = [ $.eq(['$name', '$$uid']) ] // user 和 course 的关联字段， uid为lookup的let中定义的变量字段
// 组装查询条件
if(event.userId){
  userMatch.userId = _.eq(event.userId)
}
if(event.subject){
  courseMatch.push($.eq(['$subject', event.subject]))
}
if(event.class){
  courseMatch.push($.eq(['$class', event.class]))
}
if(event.position){
  courseMatch.push($.eq(['$position', event.position]))
}

db.collection('users').aggregate()
  .match（userMatch ）
  .lookup({
    from: 'course',
    localField: 'userId',
    foreignField: 'name',
    let: {
      uid: '$userId'
    },
    pipeline: $.pipeline()
      .match(_.expr($.and(courseMatch)))
      .done(),
    as: 'courseList',
  })
  .end()

若认为该回答有用，给回答者点个[ 有用 ]，让答案帮助更多的人