在COUNT LEFT联接上找不到列

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();

$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');