在select中求和多个ActiveQuery子查询

Question

I have the following tables

Product

id name 
1  Alcohol
2  Candy
3  Soda

ProductIn

id item_no count date
1  1       10   2018/01/01
2  1       20   2018/01/07
3  2       10   2018/01/08
4  3       10   2018/01/08

ProductOut

id item_no count date 
1  1       10   2018/01/02 
2  1       10   2018/01/09 
3  2       2    2018/01/09
4  3       3    2018/01/11

I would like to get the sum of the product actual quantity by doing

select *, 
  (sum(select sum(count) from ProductIn where ProductIn.item_no = product.itemno) -
   sum(select sum(count) from ProductOut where ProductOut.item_no = product.itemno)) as availableQty
from product

currently im doing this like using ActiveQuery

$main_query = Product::find();

$data = [];
foreach ($main_query->all() as $model) {
    $query1 = ProductIn::find()
                ->filterWhere(['=', 'item_code', $model->item_no])
                ->asArray()->one();

    $query2 = ProductOut::find()
                ->filterWhere(['=', 'item_code', $model->item_no])
                ->asArray()->one();

    $allModels[$model->item_no] = ['item_no' => $model->item_no, 'name' => $model->name,  'availableQty' => ($query1 - $query2)];
}

but looping on every record is slow I wanted to combine the 3 ActiveQuery.

I was able to include the subquery to main_query by using

 $main_query->addSelect($query1)

but i cannot get the difference of the two subquery as one field.

Is there any way to do this on ActiveQuery?

Answer 1

Here's few Suggestions for you -

1. No need loop duplicate queries in forloop (i.e. $query1 & $query2)
2. Just collect all item_codes required for ProductIn and ProductOut models from $main_query with the help of ArrayHelper::getColumn
3. create 2 custom array's to store item_code with your query result(amount/quantity) in it & use this array in your foreach loop.

You will find big time difference in execution.