MongoTemplate多表关联实现与实战详解

// MongoDB 原生语法
db.orders.aggregate([
  {
    $lookup: {
      from: "customers",           // 要连接的表
      localField: "customerId",    // 本地字段
      foreignField: "_id",         // 外部表字段
      as: "customerInfo"           // 输出字段名
    }
  }
])

// Spring Data MongoDB 中的 $lookup
Aggregation.lookup("customers", "customerId", "_id", "customerInfo")

// 主表：订单
@Document(collection = "orders")
public class Order {
    @Id
    private String orderId;
    private String customerId;     // 关联字段
    private BigDecimal amount;
    private Date orderDate;
    private List<OrderItem> items;
    
    // 嵌入关联结果
    private List<Customer> customerInfo;  // 关联结果
    // getter/setter
}

// 关联表：客户
@Document(collection = "customers")
public class Customer {
    @Id
    private String customerId;
    private String name;
    private String email;
    private String phone;
    // getter/setter
}

// 订单项
public class OrderItem {
    private String productId;
    private Integer quantity;
    private BigDecimal price;
    // getter/setter
}

public List<Order> getOrdersWithCustomerInfo() {
    Aggregation aggregation = Aggregation.newAggregation(
        // 1. 连接客户表
        Aggregation.lookup(
            "customers",           // 要关联的集合名
            "customerId",          // 本地字段（orders 表的字段）
            "_id",                 // 外部字段（customers 表的字段）
            "customerInfo"         // 输出字段名
        ),
        
        // 2. 展开 customerInfo 数组（因为 $lookup 返回的是数组）
        Aggregation.unwind("customerInfo", true),  // preserveNullAndEmptyArrays = true
        
        // 3. 可选：过滤条件
        Aggregation.match(
            Criteria.where("amount").gt(1000)
                   .and("orderDate").gte(LocalDate.now().minusMonths(1))
        ),
        
        // 4. 可选：排序
        Aggregation.sort(Sort.by("orderDate").descending()),
        
        // 5. 可选：投影字段
        Aggregation.project()
            .and("orderId").as("orderId")
            .and("amount").as("amount")
            .and("orderDate").as("orderDate")
            .and("customerInfo.name").as("customerName")
            .and("customerInfo.email").as("customerEmail")
            .andExclude("_id")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        Order.class
    ).getMappedResults();
}

// 一个订单对应一个客户
public List<Order> getOneToOneOrders() {
    return mongoTemplate.aggregate(
        Aggregation.newAggregation(
            Aggregation.lookup("customers", "customerId", "_id", "customer"),
            Aggregation.unwind("customer", true),  // 展开为一对一
            Aggregation.match(Criteria.where("customer.status").is("ACTIVE"))
        ),
        "orders", 
        Order.class
    ).getMappedResults();
}

// 一个订单有多个商品，关联商品详情
public List<OrderWithProducts> getOrdersWithProducts() {
    Aggregation aggregation = Aggregation.newAggregation(
        // 1. 展开订单项数组
        Aggregation.unwind("items"),
        
        // 2. 关联商品表
        Aggregation.lookup("products", "items.productId", "_id", "productDetails"),
        
        // 3. 展开商品详情数组
        Aggregation.unwind("productDetails", true),
        
        // 4. 重新分组
        Aggregation.group("orderId")
            .first("customerId").as("customerId")
            .first("orderDate").as("orderDate")
            .push(
                new Document("productName", "$productDetails.name")
                .append("price", "$productDetails.price")
                .append("quantity", "$items.quantity")
            ).as("products")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderWithProducts.class
    ).getMappedResults();
}

// 订单-产品多对多（通过订单项关联）
public List<OrderProductDetail> getOrderProductDetails() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.unwind("items"),
        Aggregation.lookup("products", "items.productId", "_id", "productInfo"),
        Aggregation.unwind("productInfo"),
        
        // 关联产品分类
        Aggregation.lookup("categories", "productInfo.categoryId", "_id", "categoryInfo"),
        Aggregation.unwind("categoryInfo", true),
        
        // 关联供应商
        Aggregation.lookup("suppliers", "productInfo.supplierId", "_id", "supplierInfo"),
        Aggregation.unwind("supplierInfo", true),
        
        Aggregation.project()
            .and("orderId").as("orderId")
            .and("items.quantity").as("quantity")
            .and("productInfo.name").as("productName")
            .and("categoryInfo.name").as("categoryName")
            .and("supplierInfo.name").as("supplierName")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderProductDetail.class
    ).getMappedResults();
}

// 订单 -> 客户 -> 地址 -> 城市
public List<OrderWithDetails> getOrderWithFullDetails() {
    Aggregation aggregation = Aggregation.newAggregation(
        // 第一层：关联客户
        Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
        Aggregation.unwind("customerInfo", true),
        
        // 第二层：通过客户关联地址
        Aggregation.lookup("addresses", "customerInfo.addressId", "_id", "addressInfo"),
        Aggregation.unwind("addressInfo", true),
        
        // 第三层：通过地址关联城市
        Aggregation.lookup("cities", "addressInfo.cityId", "_id", "cityInfo"),
        Aggregation.unwind("cityInfo", true),
        
        // 第四层：通过城市关联省份
        Aggregation.lookup("provinces", "cityInfo.provinceId", "_id", "provinceInfo"),
        Aggregation.unwind("provinceInfo", true),
        
        Aggregation.project()
            .and("orderId").as("orderId")
            .and("customerInfo.name").as("customerName")
            .and("addressInfo.street").as("street")
            .and("cityInfo.name").as("cityName")
            .and("provinceInfo.name").as("provinceName")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderWithDetails.class
    ).getMappedResults();
}

// 双向关联：既查订单的客户，也查客户的订单
public List<CustomerWithOrders> getCustomersWithRecentOrders() {
    Aggregation aggregation = Aggregation.newAggregation(
        // 从客户表开始
        Aggregation.lookup("orders", "_id", "customerId", "customerOrders"),
        
        // 过滤最近30天有订单的客户
        Aggregation.match(
            Criteria.where("customerOrders.orderDate")
                .gte(LocalDate.now().minusDays(30))
        ),
        
        // 过滤客户的订单（只保留最近30天的）
        Aggregation.addFields()
            .addFieldWithValue("recentOrders",
                AggregationSpELExpression.expressionOf(
                    "filter(customerOrders, 'order', order.orderDate >= ?0)",
                    LocalDate.now().minusDays(30)
                )
            ).build(),
        
        // 计算订单统计
        Aggregation.project()
            .and("name").as("customerName")
            .and("recentOrders").as("orders")
            .and(ArrayOperators.Size.lengthOfArray("recentOrders")).as("orderCount")
            .and").as("totalAmount")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "customers", 
        CustomerWithOrders.class
    ).getMappedResults();
}

// MongoDB 4.0+ 支持带条件的 $lookup
public List<Order> getOrdersWithActiveCustomers() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup()
            .from("customers")
            .localField("customerId")
            .foreignField("_id")
            .pipeline(  // 在关联管道中添加条件
                Aggregation.newAggregation(
                    Aggregation.match(
                        Criteria.where("status").is("ACTIVE")
                               .and("createdDate").gte(LocalDate.now().minusYears(1))
                    )
                ).getPipeline()
            )
            .as("activeCustomer"),
        
        Aggregation.unwind("activeCustomer", true),
        Aggregation.match(Criteria.where("activeCustomer").exists(true))
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        Order.class
    ).getMappedResults();
}

// 先关联，再过滤关联结果
public List<Order> getOrdersFromVipCustomers() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
        
        // 过滤关联结果
        Aggregation.addFields()
            .addField("vipCustomer")
            .withValue(
                ConditionalOperators.filter("customerInfo")
                    .as("cust")
                    .by(
                        ComparisonOperators.Eq.valueOf("cust.level")
                            .equalToValue("VIP")
                    )
            ).build(),
        
        // 只保留有VIP客户的订单
        Aggregation.match(
            Criteria.where("vipCustomer").ne(Collections.emptyList())
        ),
        
        Aggregation.unwind("vipCustomer")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        Order.class
    ).getMappedResults();
}

// 确保关联字段有索引
@Document(collection = "orders")
@CompoundIndex(name = "customer_order_idx", def = "{'customerId': 1, 'orderDate': -1}")
public class Order {
    // ...
}

@Document(collection = "customers")
@Indexed(name = "customer_id_idx")
public class Customer {
    @Id
    private String id;
    // ...
}

// 查询时使用索引
public List<Order> getOrdersWithIndexedLookup() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.match(
            Criteria.where("orderDate").gte(LocalDate.now().minusMonths(1))
        ),
        Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
        Aggregation.unwind("customerInfo", true)
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        Order.class
    ).getMappedResults();
}

// 关联查询分页
public Page<OrderWithCustomer> getOrdersWithCustomerPage(int page, int size) {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
        Aggregation.unwind("customerInfo", true),
        Aggregation.sort(Sort.by("orderDate").descending()),
        Aggregation.skip((long) page * size),
        Aggregation.limit(size)
    );
    
    List<OrderWithCustomer> content = mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderWithCustomer.class
    ).getMappedResults();
    
    // 获取总数
    long total = mongoTemplate.count(new Query(), "orders");
    
    return new PageImpl<>(content, PageRequest.of(page, size), total);
}

// 使用自定义DTO接收关联结果
public class OrderWithCustomerDTO {
    private String orderId;
    private BigDecimal amount;
    private String customerName;
    private String customerEmail;
    
    // 构造函数用于聚合结果映射
    public OrderWithCustomerDTO(String orderId, BigDecimal amount, 
                               String customerName, String customerEmail) {
        this.orderId = orderId;
        this.amount = amount;
        this.customerName = customerName;
        this.customerEmail = customerEmail;
    }
    // getter/setter
}

// 关联查询返回DTO
public List<OrderWithCustomerDTO> getOrderCustomerDTOs() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
        Aggregation.unwind("customerInfo"),
        
        Aggregation.project()
            .and("orderId").as("orderId")
            .and("amount").as("amount")
            .and("customerInfo.name").as("customerName")
            .and("customerInfo.email").as("customerEmail")
            .andExclude("_id")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderWithCustomerDTO.class
    ).getMappedResults();
}

// 关联结果保持嵌套结构
public class OrderWithNestedCustomer {
    private String orderId;
    private Customer customer;  // 嵌套对象
    // getter/setter
}

public List<OrderWithNestedCustomer> getOrdersWithNestedCustomer() {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.lookup("customers", "customerId", "_id", "customers"),
        
        // 将 customers 数组转换为对象
        Aggregation.addFields()
            .addField("customer")
            .withValue(ArrayOperators.ArrayElemAt.arrayOf("customers").elementAt(0))
            .build(),
        
        Aggregation.project()
            .and("orderId").as("orderId")
            .and("customer").as("customer")
            .andExclude("_id")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "orders", 
        OrderWithNestedCustomer.class
    ).getMappedResults();
}

// 递归查询组织结构（查找所有下级）
public List<Employee> getEmployeeHierarchy(String managerId) {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.match(Criteria.where("_id").is(managerId)),
        
        Aggregation.graphLookup("employees")
            .startWith("$_id")
            .connectFrom("_id")
            .connectTo("managerId")
            .as("subordinates")
            .maxDepth(10)  // 最大递归深度
            .depthField("level")
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "employees", 
        Employee.class
    ).getMappedResults();
}

// 合并多个集合的查询结果
public List<Product> searchAllProducts(String keyword) {
    Aggregation aggregation = Aggregation.newAggregation(
        Aggregation.unionWith("electronics")
            .pipeline(
                Aggregation.match(
                    Criteria.where("name").regex(keyword, "i")
                )
            ),
        
        Aggregation.unionWith("clothing")
            .pipeline(
                Aggregation.match(
                    Criteria.where("name").regex(keyword, "i")
                )
            ),
        
        Aggregation.unionWith("books")
            .pipeline(
                Aggregation.match(
                    Criteria.where("title").regex(keyword, "i")
                )
            ),
        
        Aggregation.sort(Sort.by("price").ascending())
    );
    
    return mongoTemplate.aggregate(
        aggregation, 
        "products", 
        Product.class
    ).getMappedResults();
}

// 方式1：应用程序级关联（多次查询）
public List<Order> getOrdersWithCustomersAppLevel() {
    // 1. 查询订单
    List<Order> orders = mongoTemplate.find(
        Query.query(Criteria.where("status").is("ACTIVE")), 
        Order.class
    );
    
    // 2. 收集客户ID
    Set<String> customerIds = orders.stream()
        .map(Order::getCustomerId)
        .collect(Collectors.toSet());
    
    // 3. 批量查询客户
    List<Customer> customers = mongoTemplate.find(
        Query.query(Criteria.where("_id").in(customerIds)), 
        Customer.class
    );
    
    // 4. 手动组装
    Map<String, Customer> customerMap = customers.stream()
        .collect(Collectors.toMap(Customer::getId, c -> c));
    
    orders.forEach(order -> 
        order.setCustomerInfo(Collections.singletonList(
            customerMap.get(order.getCustomerId())
        ))
    );
    
    return orders;
}

// 方式2：数据库级关联（推荐，一次查询）
public List<Order> getOrdersWithCustomersDbLevel() {
    return mongoTemplate.aggregate(
        Aggregation.newAggregation(
            Aggregation.match(Criteria.where("status").is("ACTIVE")),
            Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
            Aggregation.unwind("customerInfo", true)
        ),
        "orders", 
        Order.class
    ).getMappedResults();
}

@Service
public class OrderService {
    
    @Autowired
    private MongoTemplate mongoTemplate;
    
    public OrderDetail getOrderDetail(String orderId) {
        Aggregation aggregation = Aggregation.newAggregation(
            Aggregation.match(Criteria.where("orderId").is(orderId)),
            
            // 关联客户信息
            Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
            Aggregation.unwind("customerInfo", true),
            
            // 展开订单项
            Aggregation.unwind("items"),
            
            // 关联商品信息
            Aggregation.lookup("products", "items.productId", "_id", "productInfo"),
            Aggregation.unwind("productInfo", true),
            
            // 关联商品分类
            Aggregation.lookup("categories", "productInfo.categoryId", "_id", "categoryInfo"),
            Aggregation.unwind("categoryInfo", true),
            
            // 重新分组
            Aggregation.group("orderId")
                .first("customerInfo").as("customer")
                .first("orderDate").as("orderDate")
                .first("shippingAddress").as("shippingAddress")
                .first("paymentMethod").as("paymentMethod")
                .sum("items.quantity").as("totalQuantity")
                .sum(
                    ArithmeticOperators.Multiply
                        .valueOf("items.price")
                        .multiplyBy("items.quantity")
                ).as("totalAmount")
                .push(
                    new Document("productName", "$productInfo.name")
                        .append("category", "$categoryInfo.name")
                        .append("price", "$productInfo.price")
                        .append("quantity", "$items.quantity")
                        .append("subtotal", 
                            ArithmeticOperators.Multiply
                                .valueOf("$productInfo.price")
                                .multiplyBy("$items.quantity")
                        )
                ).as("orderItems"),
            
            // 关联物流信息
            Aggregation.lookup("logistics", "orderId", "orderId", "logisticsInfo"),
            Aggregation.unwind("logisticsInfo", true),
            
            // 最终投影
            Aggregation.project()
                .and("orderId").as("orderId")
                .and("customer.name").as("customerName")
                .and("customer.phone").as("customerPhone")
                .and("orderDate").as("orderDate")
                .and("totalQuantity").as("totalQuantity")
                .and("totalAmount").as("totalAmount")
                .and("orderItems").as("items")
                .and("logisticsInfo.trackingNumber").as("trackingNumber")
                .and("logisticsInfo.status").as("deliveryStatus")
        );
        
        return mongoTemplate.aggregate(
            aggregation, 
            "orders", 
            OrderDetail.class
        ).getUniqueMappedResult();
    }
}

// 1. 只能关联同一数据库的集合
// 2. 关联字段必须有索引才能高效
// 3. 大数据集关联可能性能较差
// 4. 不支持全外连接，只支持左外连接

// 性能优化建议
Aggregation aggregation = Aggregation.newAggregation(
    // 1. 先过滤，减少数据量
    Aggregation.match(Criteria.where("status").is("ACTIVE")),
    
    // 2. 再关联
    Aggregation.lookup("customers", "customerId", "_id", "customerInfo"),
    
    // 3. 使用 allowDiskUse 处理大数据
    Aggregation.unwind("customerInfo")
).withOptions(
    AggregationOptions.builder()
        .allowDiskUse(true)
        .build()
);