php - Yii 2 ActiveDataProvider : extra field or column of balance calculation when doing query for dataProvider

Question

我正在尝试在 ActiveDataProvider(在 transactionSearch.php 模型中)中添加新字段或余额计算列

当我在 phpmyAdmin 中执行此 SQL 时，它运行良好。我从 stackoverflow 研究中得到了这段代码。谢谢

SET @bal = 0;
SELECT t.* , @bal:=t.amount+@bal AS balance  
FROM(
  SELECT *
  FROM transaction
  WHERE user_id = 1
) t

但是当我试图适应 transactionSearch.php 模型时

$query = Transaction::find();
$dataProvider = new ActiveDataProvider([
    'query' => $query
]);

这是我做的

// since I need to prepare query before starting the ActiveDataProvider query, I did this
$connectionz = Yii::$app->getDb();
$query = $connectionz->createCommand("SET @bal = 0");
$query = $query->query();
// then I run the usual query
$query = Transaction::findBySql('
                        SELECT t.* , @bal:=t.amount+@bal AS balance  
                        FROM(
                          SELECT *
                          FROM transaction
                          WHERE user_id = 1
                          ) t ')->all();
$dataProvider = new ActiveDataProvider([
    'query' => $query
]);

除了在 attributeLabel 和 rules 中添加余额外，我还在 Transaction.php 模型中添加了 setter 和 getter，但我不是确定它是对还是我没有添加任何规则？

public function setBalance($balance)
{
    $this->balance = (float) $balance;
}

public function getBalance()
{
    return '';
}

但出现错误Call to a member function andFilterWhere() on array

如有任何建议，我们将不胜感激

Answer 1

所以解决方案是

这是在 TransactionSearch.php 模型中:

$expression = new Expression('@bal:=t.amount+@bal');
$connectionz = Yii::$app->getDb();
$query = $connectionz->createCommand("SET @bal = 0");
$query = $query->query();
$query = Transaction::find()
            ->from(['t' => '(
                           SELECT *
                           FROM transaction
                           WHERE user_id = 1)
                '])
            ->select(['t.*','balance' => $expression])
            ->orderBy('date');
$dataProvider = new ActiveDataProvider([
    'query' => $query
]);

Transaction.php模型，不需要添加setter和getter，只添加:

public $balance;

在index.php的@Transaction文件夹中，@gridview widget'column'添加

'balance:currency:Balance',

就是这样。