如何在Symfony Doctrine Repository中跨子实体实现聚合计算

业务背景 #

存在带自引用关联的交易实体，规则如下：

• 买入股票时创建一条新的Trade记录
• 卖出对应持仓时新建一条Trade记录，关联到对应买入的Trade记录
• 支持同一笔买入对应多笔卖出（分批卖出场景）
  现有实体代码如下：

#[ORM\Entity(repositoryClass: TradeRepository::class)]
#[ORM\HasLifecycleCallbacks()]
class Trade
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column(type: 'integer')]
    private $id;
    
    #[ORM\Column(type: 'string', length: 10)]
    private $symbol;
    
    #[ORM\Column(type: 'float')]
    private $price;
    
    ...
    
    #[ORM\ManyToOne(targetEntity: self::class, inversedBy: 'children')]
    private $parent;
    
    #[ORM\OneToMany(targetEntity: self::class, mappedBy: 'parent', fetch: 'EAGER')]
    private $children;
}

现有能力 #

当前已实现查询所有未关联卖出记录（无子实体）的买入交易方法：

public function findAllBuyTrades(): array
{
    $qb = $this->createQueryBuilder('trade')
        ->where('trade.parent IS NULL');

    return $qb->getQuery()->execute();
}

待实现需求 #

需要编写仓库方法，返回所有已部分卖出或全部卖出的买入交易，判定规则：

• 全部卖出：所有子交易的price总和等于父交易的price值
• 部分卖出：存在子交易，且子交易price总和小于父交易price值
  已知可通过JOIN实现，但不清楚具体DQL写法，同时希望了解更优的实现方案。

已梳理的前置信息 #

• 为避免浮点数精度误差导致计算错误，price字段需要使用DECIMAL(X,X)类型替代当前的float类型
• 已写出可实现需求的原生SQL，但认为子查询写法不够优雅，且不清楚如何在Symfony/Doctrine中落地：

SELECT
    *
FROM
    trade
WHERE
    executed = (SELECT
        SUM(subtrade.executed) AS total
    FROM
        trade trade
    JOIN
        trade subtrade
    ON
        subtrade.parent_id = trade.id
    WHERE
        trade.id = XX)

实现方案 #

1. Doctrine DQL 实现（无原生SQL，直接用QueryBuilder） #

直接在TradeRepository中添加如下方法即可，相比子查询写法性能更高，且直接返回卖出状态不需要额外PHP层判断：

/**
 * 查询所有有卖出记录的买入交易（包含部分卖出、全部卖出）
 * @return array 每条结果包含trade实体、已卖出总金额soldTotal、卖出状态status（partial=部分卖出，full=全部卖出）
 */
public function findAllSoldTrades(): array
{
    $qb = $this->createQueryBuilder('t')
        // 仅筛选买入交易（无父级的记录）
        ->where('t.parent IS NULL')
        // 左关联对应的卖出子记录
        ->leftJoin('t.children', 'c')
        // 按买入交易主键分组
        ->groupBy('t.id')
        // 过滤掉完全没有卖出记录的买入交易
        ->having('COUNT(c.id) > 0')
        // 聚合计算已卖出的总金额
        ->addSelect('SUM(c.price) as soldTotal')
        // 直接在查询层判断卖出状态
        ->addSelect('CASE WHEN SUM(c.price) = t.price THEN \'full\' ELSE \'partial\' END as status');

    return $qb->getQuery()->getResult();
}

该写法利用外键关联做聚合，数据库可以直接走parent_id的索引，比相关子查询每次循环遍历计算的效率高很多。

2. 更优的性能优化方案（大数据量场景推荐） #

如果交易数据量较大，每次查询实时做SUM聚合会消耗较多数据库性能，可以采用冗余字段的方案避免运行时计算：

• 第一步：修正字段类型，将$price从float改为decimal类型，示例配置：

  #[ORM\Column(type: 'decimal', precision: 12, scale: 2)]
  private $price;
  

• 第二步：在Trade实体中新增两个冗余字段：
  • $soldTotal：decimal类型，记录该笔买入已卖出的总金额
  • $sellStatus：string类型，固定取值unsold/partial/full，标记卖出状态
• 第三步：在新增卖出交易的业务逻辑中（或通过Doctrine生命周期回调），每次保存卖出子记录时，同步更新对应父交易的soldTotal和sellStatus字段
  优化后查询已卖出交易时，不需要任何JOIN和聚合计算，直接加where条件判断sellStatus即可，查询性能会有数量级提升。

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