mongodb 多表关联查询
需求mongodb库中有三张表,需求:查询表A中的数据,但这些数据的threadId字段,不能存在于表B和表C中,做一个过滤查询,需要分页和排序。mongodb 命令实现{"aggregate": "__collection__","pipeline": [{"$match": {"open": 1,"closed": 0}},{"$lookup": {"from"
·
需求
mongodb库中有三张表,需求:查询表A中的数据,但这些数据的threadId字段,不能存在于表B和表C中,做一个过滤查询,需要分页和排序。
mongodb 命令实现
{
"aggregate": "__collection__",
"pipeline": [
{
"$match": {
"open": 1,
"closed": 0
}
},
{
"$lookup": {
"from": "B",
"localField": "threadId",
"foreignField": "threadId",
"as": "annotation"
}
},
{
"$unwind": {
"path": "$annotation",
"preserveNullAndEmptyArrays": true
}
},
{
"$match": {
"$or": [
{
"annotation": null
},
{
"annotation.reBacked": 1
}
]
}
},
{
"$lookup": {
"from": "C",
"localField": "threadId",
"foreignField": "threadId",
"as": "queue"
}
},
{
"$unwind": {
"path": "$queue",
"preserveNullAndEmptyArrays": true
}
},
{
"$match": {
"queue": null
}
},
{
"$sort": {
"createTime": -1
}
},
{
"$skip": 0
},
{
"$limit": 10
}
]
}
其实就是多个$lookup
的累加,需要配合$unwind
使用。
java springboot 实现
pom.xml文件依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
接口实现代码如下:
@Override
public List<ThreadEntity> getThreadList(ThreadVo threadVo) throws Exception {
String source = threadVo.getSource();
Integer page = threadVo.getPage();
Integer pageSize = threadVo.getPageSize();
Integer type = threadVo.getType();
LookupOperation lookupOperation1 = LookupOperation.newLookup()
.from("B")
.localField("threadId")
.foreignField("threadId")
.as("annotation");
LookupOperation lookupOperation2 = LookupOperation.newLookup()
.from("C")
.localField("threadId")
.foreignField("threadId")
.as("queue");
UnwindOperation unwind1 = UnwindOperation.newUnwind().path("annotation").noArrayIndex().preserveNullAndEmptyArrays();
UnwindOperation unwind2 = UnwindOperation.newUnwind().path("queue").noArrayIndex().preserveNullAndEmptyArrays();
Criteria criteria = Criteria.where("open").is(1).and("closed").is(0);
AggregationOperation match = Aggregation.match(criteria);
Criteria criteria1 = new Criteria();
criteria1.orOperator(
Criteria.where("annotation").is(null),
Criteria.where("annotation.reBacked").is(1)
);
Criteria criteria2 = new Criteria().where("queue").is(null);
AggregationOperation match1 = Aggregation.match(criteria1);
AggregationOperation match2 = Aggregation.match(criteria2);
AggregationOperation sort = Aggregation.sort(Sort.by(Sort.Order.desc(annotationLabelService.getGetConntentType(type))));
AggregationOperation skip = Aggregation.skip((page - 1) * pageSize);
AggregationOperation limit = Aggregation.limit(pageSize);
Aggregation aggregation = Aggregation.newAggregation(match, lookupOperation1, unwind1, match1, lookupOperation2, unwind2, match2, sort, skip, limit);
List<ThreadEntity> list = (List<ThreadEntity>) mongoUtils.lookUp(aggregation, "A", ThreadEntity.class);
return list;
}
更多推荐
已为社区贡献2条内容
所有评论(0)