mongo关联查询时筛选从表的条件

最新推荐文章于 2024-08-14 02:18:29 发布

ShenSJay

最新推荐文章于 2024-08-14 02:18:29 发布

阅读量2.6k

点赞数 1

文章标签： mongo MongoTemplate 关联查询筛选从表

本文链接：https://blog.csdn.net/qq_32234855/article/details/103501460

版权

db.主表名.aggregate([{
        $lookup:{
            from:"从表名",
            localField:"主键",
            foreignField:"外键",
            as:"asField" //查询结果中外键集合对应集合名,与sql的as类似
        }
    },
    {$match:{"主表字段":"条件","asField.从表字段":"条件"} }])

注意match要在lookup后，从表筛选条件需要用lookup中as关键字所取用的名称+从表字段名选取

db.adv.aggregate([{
    $lookup:{
        from:"company",
        localField:"name",
        foreignField:"companyName",
        as:"addr"
   	 }
  },
  {$match:{"address":"北京","addr.address_province":"北京","addr.address_city":"北京市"，"addr.address_province":"海淀区"}}])

mongoTemplate中使用：

import java.util.List;
import com.mongodb.BasicDBObject;
import org.springframework.stereotype.Repository;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.LimitOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;

public class AdvertisementDaoImpl implements AdvertisementDao {
    @Autowired
    private MongoTemplate mongoTemplate;
    @Override
    public List advCount(String prov, String city, String cityArea) {
        /**
         * db.final_adv_all.aggregate([{
         *     $lookup:{
         *         from:"companyFinal",
         *         localField:"e_nameOfAdvertiser",
         *         foreignField:"company",
         *         as:"addr"
         *     }
         * },{$match:{"e_address":"","addr.address_province":"","addr.address_city":""}}])
         */
        AggregationOperation match = null;
        Criteria criteria = new Criteria();
        Criteria criteriaEaddress = new Criteria();
        Criteria criteriaProv = new Criteria();
        Criteria criteriaCity = new Criteria();
        Criteria criteriaCityArea = new Criteria();
        LookupOperation lookup = LookupOperation.newLookup().
                from("companyFinal").
                localField("e_nameOfAdvertiser").
                foreignField("company").
                as("addr");
        criteriaEaddress = Criteria.where("e_address").is(prov);
        criteriaProv = Criteria.where("addr.address_province").is(prov);
        criteriaCity = Criteria.where("addr.address_city").is(city);
        if (!"".equals(cityArea)){
            criteriaCityArea = Criteria.where("addr.address_province").is(cityArea);
            criteria.andOperator(criteriaCityArea);
        }
        criteria.andOperator(criteriaEaddress,criteriaProv,criteriaCity);
        match = Aggregation.match(criteria);
//        LimitOperation limitOperation = Aggregation.limit(15);
        Aggregation aggregation = Aggregation.newAggregation(lookup, match);
//        Aggregation aggregation = Aggregation.newAggregation(match, lookup); 和上一行代码的差异，谁在前先执行谁；match在前，查询无结果！！！
        List resultList = this.mongoTemplate.aggregate(aggregation, "final_adv_all", BasicDBObject.class).getMappedResults();
        return resultList;
    }
}