Sub Query

subquery.php

<?php

$queryGM = (new Query())
    ->select(['gmd.product_id', 'total' => 'sum(gmd.qty)'])
    ->from(['gm' => '{{%goods_movement}}'])
    ->innerJoin(['gmd' => '{{%goods_movement_dtl}}'], '[[gmd.movement_id]]=[[gm.id]]')
    ->where(['gm.status' => 20, 'gm.reff_type' => self::REFF_SELF, 'gm.reff_id' => $this->id])
    ->groupBy(['gmd.product_id']);
$queryItem = (new Query())
    ->select(['md.product_id', 'md.cogs', 'md.qty', 'md.uom_id', 'g.total'])
    ->from(['md' => '{{%goods_movement_dtl}}'])
    ->leftJoin(['g' => $queryGM], '[[g.product_id]]=[[md.product_id]]')
    ->where(['md.movement_id' => $this->id]);
    
    foreach ($queryItem->all() as $item) {
        ...