我正在尝试在ActivedAtaProvider(在Transactionsearch.php模型中)添加新的字段或平衡计算列
当我将此SQL做到phpmyadmin时,它可以很好地工作。我从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中添加余额&规则,我还添加了TransAction.php模型setter和getter,但是我是> not 确定是正确的,或者i 是否 spect 添加任何规则?
public function setBalance($balance)
{
$this->balance = (float) $balance;
}
public function getBalance()
{
return '';
}
但出现错误Call to a member function andFilterWhere() on array
任何建议都将不胜感激
,因此解决方案是
这是在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文件夹中
'balance:currency:Balance',
就是这样。
1)使用sqldataprovider
$connectionz = Yii::$app->getDb();
$query = $connectionz->createCommand("SET @bal = 0");
$query = $query->query();
$query = Transaction::findBySql('
SELECT t.* , @bal:=t.amount+@bal AS balance
FROM(
SELECT *
FROM transaction
WHERE user_id = 1
) t ');
$dataProvider = new SqlDataProvider([
'query' => $query
]);
或
2)在交易类中添加公共财产余额。然后在SQL中:
$connectionz = Yii::$app->getDb();
$query = $connectionz->createCommand("SET @bal = 0");
$query = $query->query();
$query = Transaction::find()->where(your condition here)
->select([transaction.*,'balance'=>' @bal:=t.amount+@bal'])
$dataProvider = new ActiveDataProvider([
'query' => $query
]);
然后您可以这样使用:
$model->balance