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 JOINcompany
b
ON a.Referral=b.Id LEFT JOINtransaction
c
ON a.Id = c.UserId WHERE (a.Role = 0) AND (c.TransactionCount > 0) GROUP BYa
.Id
ORDER BYId
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