mongoTemplate.aggregate() 聚合查询，关联查询

聚合方法实现了什么功能

条件查询，分组，统计，关联查询，排序，分页，返回指定字段

聚合方法：

AggregationResults<DocumentEntity> results = mongoTemplate.aggregate(aggregation, TableNameUtils.getDocTableName(request.getCompanyId()), DocumentEntity.class);

源码：
@Override
public <O> AggregationResults<O> aggregate(Aggregation aggregation, String collectionName, Class<O> outputType) {
   return aggregate(aggregation, collectionName, outputType, null);
}
参数1：Aggregation，看下这个对象的方法，可以看到入参有两种，集合/数组

public static Aggregation newAggregation(List<? extends AggregationOperation> operations) {
   return newAggregation(operations.toArray(new AggregationOperation[operations.size()]));
}

public static Aggregation newAggregation(AggregationOperation... operations) {
   return new Aggregation(operations);
}

1，关联查询

需求：有三张表，档案表，发票表，文件表，发票表和文件表通过字段文档流水号（documentSerialNum），与档案表字段（serialNum）流水号关联，现在要根据serialNum查询文档表及其下面的发票和发票信息（1：n）。

代码：

private List<DocumentEntity> getDocumentEntities(BaseRequestModel request, List<String> serialNumS) {
    // 文件和发票表通过外键查询
    LookupOperation lookupOperation = LookupOperation.newLookup().
            from("table_file").
            localField("serialNum").
            foreignField("documentSerialNum").
            as("docs");
    LookupOperation lookupOperationinv = LookupOperation.newLookup().
            from("table_inv").
            localField("serialNum").
            foreignField("documentSerialNum").
            as("docs2");

    // 拼装具体查询信息
    Criteria docCri = Criteria.where("docs").not().size(0);
    docCri.and("serialNum").in(serialNumS);
    docCri.and("isDel").is(IsDelEnum.NO.getValue());
    docCri.and("docs.isDel").is(IsDelEnum.NO.getValue());
    docCri.and("docs2.isDel").is(IsDelEnum.NO.getValue());
    AggregationOperation match = Aggregation.match(docCri);

    // 把条件封装成List
    List<AggregationOperation> operations = new ArrayList<>();
    operations.add(lookupOperation);
    operations.add(lookupOperationinv);
    operations.add(match);
    
    // 构建 Aggregation 
    Aggregation aggregation = Aggregation.newAggregation(operations);

    // 执行查询
    AggregationResults<DocumentEntity> results = mongoTemplate.aggregate(aggregation, "table_doc", DocumentEntity.class);

    // 或者入参为数组
    Aggregation aggregation = Aggregation.newAggregation(lookupOperation,lookupOperationinv,Aggregation.match(docCri));

    return results.getMappedResults();
}

特别注意的一点：主字段和外键对应哪个表，指的是mongoTemplate.aggregate()，方法中的表

localField：table_doc主字段 serialNum

foreignField：table_inv table_file 外键 documentSerialNum

LookupOperation lookupOperation = LookupOperation.newLookup(). from("table_file"). localField("serialNum"). foreignField("documentSerialNum"). as("docs");

2，返回指定字段，分组，统计，排序，分页

详细见原文：

https://www.jianshu.com/p/78b96ca40927

https://www.cnblogs.com/wslook/p/9831842.html

需求：在订单表中，根据buyerNick分组，统计每个buyerNick的电话、地址、支付总金额以及总商品数，返回结果是CustomerDetail。

/*
 * project:列出所有本次查询的字段，包括查询条件的字段和需要搜索的字段；
 * match:搜索条件criteria
 * unwind：某一个字段是集合，将该字段分解成数组
 * group：分组的字段，以及聚合相关查询
 *      sum：求和(同sql查询)
 *      count：数量(同sql查询)
 *      as:别名(同sql查询)
 *      addToSet：将符合的字段值添加到一个集合或数组中
 * sort：排序
 * skip&limit：分页查询
 */
public List<CustomerDetail> customerDetailList(Integer pageNum,String userId,String buyerNick,String itemId,List<String> phones) throws Exception{
    Criteria criteria = Criteria.where("userId").is(userId);
    Integer pageSize = 10;
    Integer startRows = (pageNum - 1) * pageSize;
    if(buyerNick != null && !"".equals(buyerNick)){
        criteria.and("buyerNick").is(buyerNick);
    }
    if(phones != null && phones.size() > 0){
        criteria.and("mobile").in(phoneList);
    }
    if(itemId != null && !"".equals(itemId)){
        criteria.and("orders.numIid").is(itemId);
    }
    Aggregation customerAgg = Aggregation.newAggregation(
            Aggregation.project("buyerNick","payment","num","tid","userId","address","mobile","orders"),
            Aggregation.match(criteria),
　　　　　　　　　　　　　　 Aggregation.unwind("orders"),
            Aggregation.group("buyerNick").first("buyerNick").as("buyerNick").first("mobile").as("mobile").
            first("address").as("address").sum("payment").as("totalPayment").sum("num").as("itemNum").count().as("orderNum"),
            Aggregation.sort(new Sort(new Sort.Order(Sort.Direction.DESC, "totalPayment"))),
            Aggregation.skip(startRows),
            Aggregation.limit(pageSize)
            );
    List<CustomerDetail> customerList = tradeRepository.findAggregateList(new Query(criteria), userId, customerAgg,CustomerDetail.class);
    return customerList;
}

public <T> List<T> findAggregateList(Query query,String userNickName, Aggregation aggregation,Class<T> clazz) {
    AggregationResults<T> aggregate = this.mongoTemplate.aggregate(aggregation, collectionName, clazz);
    List<T> customerDetails = aggregate.getMappedResults();
    return customerDetails;
}

3，三张表在java中实体如何关联的：字表定义为List

@Data
@ToString
public class DocumentEntity extends MongoBaseEntity {

    /**
     * 档案名称
     */
    private String name;
    /**
     * 档案类型
     */
    private int type;
    /**
     * 所属公司
     */
    private Integer companyId;

    /**
     * 流水号
     */
    private String serialNum;
    /**
     * 档案编号
     */
    private String code;
    /**
     * 状态
     */
    private String status;
    /**
     * 保密等级
     */
    private String secrecyLevel;
    /**
     * 资料数量
     */
    private Long detailNum;
    /**
     * 描述
     */
    private String description;
    /**
     * 自定义字段 map集合,
     */
    private Map<String,Object> documentMap;

    /**
     * 文件集合
     */
    private List<FileEntity> fileList;
    /**
     * 发票集合
     */
    private List<InvoiceEntity> InvoiceList;


}