在COUNT LEFT JOIN上找不到列

i'm trying to search user with join introducing company, and count total topup transaction with the following code

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

but yii throw me error like this

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

and i try to look at the query for long time, but i cant see what is the problem i don't know why it will throw me error ,even i try

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

but get the same error just column name differrent

You can't use aliases in WHERE clauses.

Replacing

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

with

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

or

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

should do the trick.

Your TransactionCount is not real column, but product after query execution. That is why SQL can't found it.

Replace c.TransactionCount > 0 with COUNT(c.Id) > 0
Or better use HAVING TransactionCount > 0. In Yii having will looks like ->having('TransactionCount > :desiredCount', [':desiredCount' => 0])

Where cluase always execute before group_by --aggregate function

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 (count(c.Id) > 0) GROUP BY a.Id ORDER BY Id LIMIT 20