Invalid argument for foreach CGridView

Yii 1.1 General Discussion
1

I am getting an error invalid argument supplied for foreach

I am trying to Outer Join two tables

Player and Player Season

I want all Players Historically but if they are set up for the current season I want to show the current season settings. I have an Inner Join on Player Season where Season_id is this season then outer join with Player. I did the inner join because I couldn’t think of how to do the select without it. In MySql workbench the query works. I am looking for suggestions on how to see the sql I’m sending or suggestions on what may be the invalid argument. The error line is C:\xampp\yii\framework\db\ar\CActiveFinder.php(838)

SQL that works is:


select p.id,p.last_name,p.first_name,p.address,p.phone_1,p.phone_2,p.email,ps.* from tbl_player_season ps INNER JOIN (SELECT t.player_id FROM tbl_player_season t WHERE t.season_id = 2)

as slt ON slt.player_id = ps.player_id RIGHT JOIN tbl_player p ON ps.player_id = p.id;

In my model:




public function searchPlayerJoin()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;

		$criteria->join='INNER JOIN (SELECT * FROM tbl_player_season ps WHERE ps.season_id = '.SeasonLeague::model()->getCurrentSeasonID().')

										as slt ON slt.player_id = t.player_id 

										RIGHT JOIN tbl_player p ON t.player_id = p.id';

		$criteria->compare('player_id',$this->player_id);

		$criteria->compare('season_id',$this->season_id);

		$criteria->compare('comment',$this->comment,true);

		$criteria->compare('date_of_application',$this->date_of_application,true);

		$criteria->compare('current_league_id',$this->current_league_id);

		$criteria->compare('number_of_seasons',$this->number_of_seasons);

		$criteria->compare('pitcher_catcher',$this->pitcher_catcher,true);

		$criteria->compare('jersey_size',$this->jersey_size,true);

		$criteria->compare('pant_size',$this->pant_size,true);

		$criteria->compare('short_size',$this->short_size,true);

		$criteria->compare('tshirt_size',$this->tshirt_size,true);

		$criteria->compare('league.league_name',$this->league_name,true);

		$criteria->compare('player.first_name',$this->first_name,true);

		$criteria->compare('player.last_name',$this->last_name,true);

		$criteria->with=array('currentLeague');

		


		return new CActiveDataProvider($this, array(

			'criteria'=>$criteria,

		));

	}

In my view:




$playerSeasonInstance = new PlayerSeason();

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

     'dataProvider'=>$playerSeasonInstance->searchPlayerJoin(),

     'id'=>'currentplayers-grid',

	 'filter'=>$playerSeasonInstance,

     'columns'=>array(

           'last_name',

		   'first_name',

           /*'league_name',

		   'date_of_application',

		   'comment',*/

     )));
2

Let me change the question

How do I translate this to cdbcriteria




select * from tbl_player_season t INNER JOIN 

(select * from tbl_player_season s where s.season_id=2) as slt on slt.player_id=t.player_id 

RIGHT OUTER JOIN tbl_player p on t.player_id = p.id;
3

Perhaps this post could help

here

4

I have been able to get the values I want in my CGridView to display as expected. The issue I am having now is that the search/filter is not working. Is there an easy way to debug around the search/filter?

Here is the code I have

I defined the following variables




public $p_last_name;

	public $p_first_name;

	public $cl_league_name;

Then I created my search method that contains a join of several tables




public function searchPlayerJoin()

	{

		// Warning: Please modify the following code to remove attributes that

		// should not be searched.


		$criteria=new CDbCriteria;

		$criteria->select='

					t.comment,

					t.date_of_application,

					league.league_name as cl_league_name,

					player.first_name as p_first_name,

					player.last_name as p_last_name';

		$criteria->join='LEFT JOIN (select * from tbl_player_season s where s.season_id='.SeasonLeague::model()->getCurrentSeasonID().') as slt on slt.player_id=t.player_id

					RIGHT JOIN tbl_player player on slt.player_id = player.id 

					LEFT JOIN tbl_league league on league.id = t.current_league_id';

		

							

		$criteria->compare('t.comment',$this->comment,true);

		$criteria->compare('t.date_of_application',$this->date_of_application,true);

		$criteria->compare('league.league_name',$this->cl_league_name,true);

		$criteria->compare('player.first_name',$this->p_first_name,true);

		$criteria->compare('player.last_name',$this->p_last_name,true);

		$criteria->order='p_last_name asc, p_first_name asc';

		

		return new CActiveDataProvider(get_class($this), array(

			'criteria'=>$criteria,));

		

		

		

	}

Now I add that to my CGridView




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

	'id'=>'player-grid',

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

	'filter'=>$model,

	'columns'=>array(

		'p_last_name',

		'p_first_name',

		'cl_league_name',

		'date_of_application',

		'comment',

		array(

			'class'=>'CButtonColumn',

			'template'=>'{update} {delete}',

				     'deleteConfirmation'=>"js:'Player '+$(this).parent().parent().children(':first-child').text()+' will be deleted! Continue?'",

				     'buttons'=>array(

				          'update'=>array(

				               'label'=>'Update',

				               'url'=>'Yii::app()->controller->createUrl("/currentplayers/update/".$data->player_id)',

								),

				           'delete'=>array(

				               'label'=>'Delete',

				               'url'=>'Yii::app()->controller->createUrl("/currentplayers/delete/".$data->player_id)',


				           		),

				     	

					),

		),

	),

Here is my controller




public function actionAdmin()

	{

		$model=new PlayerSeason('search');

		$this->render('admin',array('model'=>$model));

	}