How to create subtraction with expression objects?

Viewed 23

How can I get the proper result for TotalQt?

$query = $this->ItemLedgers
    ->find()
    ->where([
        'ItemLedgers.processed_on <=' => date("Y-m-d",strtotime($to_date))
    ]);

$totalInCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['in_out' => 'In']),
        $query->newExpr()->add(['quantity']),
        'integer'
    );

$totalOutCase = $query->newExpr()
    ->addCase(
        $query->newExpr()->add(['in_out' => 'Out']),
        $query->newExpr()->add(['quantity']),
        'integer'
    );
    
$query
    ->select([
        'total_in' => $query->func()->sum($totalInCase),
        'total_out' => $query->func()->sum($totalOutCase),
        'id',
        'item_id',
        'TotalQt' => $query->func()->sum($totalInCase) - $query->func()->sum($totalOutCase),
    ])
    ->contain(['Items' => function ($q) use($where) { 
        return $q
            ->where($where)
            ->contain(['Units']);
    }])
    ->where(['company_id' => $st_company_id])
    ->group('item_id')
    ->autoFields(true)
    ->where($where);
0 Answers
Related