Yii Join In Two Models

Hello Yii Developers,

I want to write this sql query in yii


$criteria=new CDbCriteria;


`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().



try this


 $criteria = new CDbCriteria;

            $criteria->select = 'm.*,b.billing_id,b.billing_date,b.member_id ';

            $criteria->join = ' LEFT JOIN `billing` AS `tu` ON b.member_id = m.id';

            $criteria->addCondition("b.billing_date <= DATE_SUB( NOW(),INTERVAL 40 DAY) AND m.status='a'");

            $resultSet    =    Members::model()->findAll($criteria);

            

            return $resultSet;

Thanks for reply,

I got this error:


Unknown table 'm'

i think it’s may be m.status field so please chnage this syntax


$criteria->addCondition("b.billing_date <= DATE_SUB( NOW(),INTERVAL 40 DAY) AND status='a'");

i am not sure it’s works or not.

Many Thanks @Ankit Modi.

In members controller I have write this funciton




  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

My Cgrid code is below,




<?php

  $this->widget('zii.widgets.grid.CGridView', array(

	'id'=>'members-grid',

	'selectableRows'=>2,

	'dataProvider'=>$model->showActiveMembersButNotPaying(),

	'filter'=>$model,

	'columns'=>array(	

		array(

	        'value'=>'$data->id',

			'class'=>'CCheckBoxColumn',

        ),

         'id',

         'username',

          array(

        	'class'=>'CButtonColumn',		

		),

	),

)); 

?>

<?php $this->endWidget(); 



How I show these results in CGrid view ?

Thanks in Advance.

Many Many Thanks @Ankit Modi.

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,

			));

Hi Shahzad Thathal,

cool :rolleyes: