`SELECT m.*,b.billing_id,b.billing_date,b.member_id
FROM members m, billing b
WHERE b.billing_date <= DATE_SUB( NOW(),INTERVAL 40 DAY) AND b.member_id = m.id AND m.status='a'`
I have two models members and billing,
id is (primary key) of members
billing_id is (primary key) of billing and member_id (foreign key) of billing.
in billing model relation array is
'members' => array(self::BELONGS_TO, 'Members', 'member_id'),
in members model relation array is
'billing'=> array(self::HAS_MANY, 'Billing', 'member_id'),
How to write this query in $criteria=new CDbCriteria; show data in CGridView?
I want to make a new function in members model instead of function search().
public function actionShowActiveNotPayingMembers(){
$model=new Members('showActiveMembersButNotPaying');
$this->render('active_notpaying_members',array(
'model'=>$model,
));
}
In members model I have write this function.
public function showActiveMembersButNotPaying(){
$criteria = new CDbCriteria;
$criteria->select = '*,tu.billing_id,tu.billing_date,tu.member_id ';
$criteria->join = ' LEFT JOIN `billing` AS `tu` ON tu.member_id = id';
$criteria->addCondition("tu.billing_date <= DATE_SUB( NOW(),INTERVAL 40 DAY) AND status='a'");
$resultSet = Members::model()->findAll($criteria);
return $resultSet;
}
This query run successfully but now when I want to show this data in CGridView I got this error:
Fatal error: Call to a member function getData() on a non-object in D:\wamp\www\bulkresponse\yii\framework\zii\widgets\CBaseListView.php on line 105
I have changed my members model function and now issue solved.
public function showActiveMembersButNotPaying(){
$criteria = new CDbCriteria;
$criteria->select = '*,tu.billing_id,tu.billing_date,tu.member_id ';
$criteria->join = ' LEFT JOIN `billing` AS `tu` ON tu.member_id = id';
$criteria->addCondition("tu.billing_date <= DATE_SUB( NOW(),INTERVAL 40 DAY) AND status='a'");
return new CActiveDataProvider(get_class($this),array(
'pagination'=>array(
'pageSize'=> Yii::app()->user->getState('pageSize',Yii::app()->params['defaultPageSize']),
),
'criteria'=>$criteria,
));