php - 在 COUNT LEFT JOIN 上找不到列

Question

我正在尝试搜索加入介绍公司的用户，并使用以下代码计算充值交易总额

$data = Yii::app()->db->createCommand()->select('a.Id, a.UserName, a.Name, a.Sex, a.DOB, a.Email, a.ContactNo, a.Credit, a.EmailVerified, a.Level, a.CreatedTime, a.Active, a.Referral, a.ReceivedFirstTimeDepositBonus, b.Name AS ReferralName, COUNT(c.Id) AS TransactionCount')->from('user a')->leftJoin('company b', 'a.Referral=b.Id')->leftjoin('transaction c', 'a.Id = c.UserId')->group('a.Id');

$data = $data->andWhere('a.Role = 0');
$data = $data->andWhere('c.TransactionCount > 0');
$data = $data->queryAll();

但是 yii 给我抛出这样的错误

Column not found: 1054 Unknown column 'c.TransactionCount' in 'where clause'. The SQL statement executed was: SELECT a.Id, a.UserName, a.Name, a.Sex, a.DOB, a.Email, a.ContactNo, a.Credit, a.EmailVerified, a.Level, a.CreatedTime, a.Active, a.Referral, a.ReceivedFirstTimeDepositBonus, b.Name AS ReferralName, COUNT(c.Id) AS TransactionCount FROM user a LEFT JOIN company b ON a.Referral=b.Id LEFT JOIN transaction c ON a.Id = c.UserId WHERE (a.Role = 0) AND (c.TransactionCount > 0) GROUP BY a.Id ORDER BY Id LIMIT 20

我尝试长时间查看查询，但我看不出问题是什么，我不知道为什么它会抛出错误，即使我尝试

$data = $data->andWhere('TransactionCount > 0');

但得到相同的错误只是列名称不同

Answer 1

不能在 WHERE 子句中使用别名。

更换

$data = $data->andWhere('c.TransactionCount > 0');

与

$data = $data->having('COUNT(c.Id) > 0');

或

$data = $data->having('TransactionCount > 0');

应该可以解决问题。