Shopware6中使用Criteria.addAssociation时如何避免订单总额计算错误？

我通过Criteria.addAggregation计算订单总额，但调用criteria.addAssociation('documents.documentType')时，左连接会导致订单数据重复，最终总额计算结果偏差。

当前生成的SQL #

SELECT SUM(`order`.`amount_total`) as `orderAmountTotal`
FROM `order`
         LEFT JOIN `document` `order.documents` ON `order`.`id` = `order.documents`.`order_id`
         LEFT JOIN `document_type` `order.documents.documentType`
                   ON `order.documents`.`document_type_id` = `order.documents.documentType`.`id`
WHERE (`order`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425)
  AND (((`order.documents.documentType`.`technical_name` = 'invoice' AND
         (`order.documents`.`created_at` >= '2022-05-31T22:00:00.852Z' AND
          `order.documents`.`created_at` <= '2023-06-01T10:04:08.852Z'))));

期望生成的SQL #

SELECT SUM(`order`.`amount_total`) as `orderAmountTotal`
FROM (SELECT `order`.`amount_total`
      FROM `order`
               LEFT JOIN `document` `order.documents` ON `order`.`id` = `order.documents`.`order_id`
               LEFT JOIN `document_type` `order.documents.documentType`
                         ON `order.documents`.`document_type_id` = `order.documents.documentType`.`id`
      WHERE (`order`.`version_id` = 0x0FA91CE3E96A4BC2BE4BD9CE752C3425)
        AND (((`order.documents.documentType`.`technical_name` = 'invoice' AND
               (`order.documents`.`created_at` >= '2022-05-31T22:00:00.852Z' AND
                `order.documents`.`created_at` <= '2023-06-01T10:04:08.852Z'))))
      GROUP BY `order`.`id`) `order`;

疑问 #

是否必须编写自定义控制器处理？有没有其他方案？希望仅通过单个Criteria解决，但目前无法实现。

当前使用的代码（重写sw-order-list） #

orderCriteria() {
    const defaultCriteria = this.$super('orderCriteria');
    defaultCriteria.addAssociation('documents.documentType');
    defaultCriteria.addAggregation(Criteria.sum('orderAmountTotal', 'amountTotal'));
    return defaultCriteria;
},

不需要编写自定义控制器，以下两种方案均能通过Criteria实现需求：

方案1：用Exists子查询替代关联（推荐） #

将左连接替换为Exists条件，避免因多关联行导致订单数据重复，同时保留过滤逻辑：

orderCriteria() {
    const defaultCriteria = this.$super('orderCriteria');
    
    // 移除原关联，改用Exists子查询过滤符合条件的订单
    const documentFilter = Criteria.exists('documents', (subCriteria) => {
        subCriteria.addAssociation('documentType');
        subCriteria.addFilter(Criteria.equals('documentType.technicalName', 'invoice'));
        subCriteria.addFilter(Criteria.between('createdAt', '2022-05-31T22:00:00.852Z', '2023-06-01T10:04:08.852Z'));
    });
    
    defaultCriteria.addFilter(documentFilter);
    defaultCriteria.addAggregation(Criteria.sum('orderAmountTotal', 'amountTotal'));
    return defaultCriteria;
}

该方案生成的SQL会使用EXISTS子查询替代左连接，主查询中每个订单仅出现一次，聚合结果准确。

方案2：子查询分组后再聚合 #

通过嵌套子查询先按订单ID分组去重，再外层执行求和聚合，实现你期望的SQL结构：

orderCriteria() {
    // 子查询：获取符合条件的订单总额并按ID分组去重
    const subCriteria = new Criteria();
    subCriteria.addAssociation('documents.documentType');
    subCriteria.addFilter(Criteria.equals('versionId', '0x0FA91CE3E96A4BC2BE4BD9CE752C3425'));
    subCriteria.addFilter(Criteria.equals('documents.documentType.technicalName', 'invoice'));
    subCriteria.addFilter(Criteria.between('documents.createdAt', '2022-05-31T22:00:00.852Z', '2023-06-01T10:04:08.852Z'));
    subCriteria.addGroupBy('id');
    subCriteria.addSelect('amountTotal');

    // 外层查询：对去重后的订单总额求和
    const defaultCriteria = new Criteria();
    defaultCriteria.setQuery(subCriteria);
    defaultCriteria.addAggregation(Criteria.sum('orderAmountTotal', 'amountTotal'));
    
    return defaultCriteria;
}

注：该方案需确认你的Shopware版本支持Criteria嵌套子查询功能（Shopware 6.4+版本通常支持）。

内容的提问来源于stack exchange，提问作者Ümit