在select中求和多个ActiveQuery子查询

Question

我有以下表格

产品

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

我想得到产品实际数量的总和通过进行

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

目前我正在使用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)];
}

但是在每个记录上循环很慢，我想结合3 ActiveQuery。

我能够通过使用将子查询包括到main_query中

$main_query->addSelect($query1)

但我无法将两个子查询的差异作为一个字段。

有什么方法可以在ActiveQuery上做到这一点吗？

Answer 1

以下是一些建议-

1. forloop中无需循环重复查询(即$query1&$query2(
2. 只需在ArrayHelper::getColumn的帮助下从$main_query中收集ProductIn和ProductOut模型所需的所有item_codes
3. 创建2个自定义数组来存储item_code，其中包含您的查询结果(金额/数量(&在foreach循环中使用此数组

您会发现执行过程中存在很大的时间差。

Answer 2

您可以尝试以下查询：

$connection = Yii::$app->db;
$model = $connection->createCommand('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');
$products = $model->queryAll();

您需要导入查询类：

use yiidbQuery;