DB Criteria with relation alias

Yii 1.1 General Discussion
1

Hi All,

Quick one for someone out there (im sure!). Ive got 3 tables (of interest):

Teams->TeamsInCompetition->Fixtures

So (In English) a Team is a member of a competition and a competition/Team has a fixture (game).

When im displaying the fixtures in a CGridView I want to say which team is playing which team instead of ids e.g.

Competition: Barclays Premier League

Fixture: Arsenal (Teams Table) Vs Man Utd (Teams Table).

…

Ive got the display part sorted but i get an ambigious error when trying to set up the filter as its a relation of a relation i.e. TeamsInCompetition.Teams for both Arsenal and Man Utd. I know i need to use an alias to resolve it but am not sure how to configure an alias for a relation of a relation.

What i have is in the Fixtures Model search method:




...


public function relations()

{

	return array(

		'homeCompTeam' => array(self::BELONGS_TO, 'TeamsInCompetition', 'homeCompTeamId'),

		'awayCompTeam' => array(self::BELONGS_TO, 'TeamsInCompetition', 'awayCompTeamId')

	);

}


public function search()

{

	$criteria=new CDbCriteria;


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

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

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

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

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

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

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

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

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

	

	$activeDataProvider = new CActiveDataProvider($this, array(

		'criteria'=>$criteria,

	));

	

	$activeDataProvider->getCriteria()->with = array(

		'homeCompTeam',

		'awayCompTeam'

	);

	$activeDataProvider->getCriteria()->compare('teamName',$this->homeTeamName, true );

	$activeDataProvider->getCriteria()->compare('teamName',$this->awayTeamName, true );

	

	$activeDataProvider->getSort()->attributes = array(

		'homeTeamName' => array(

			'asc' => 'teamName',

			'desc' => 'teamName DESC',

		),

		'awayTeamName' => array(

			'asc' => 'teamName',

			'desc' => 'teamName DESC',

		),

		'*',

	);

		

	return $activeDataProvider;

}


	public $homeTeamName;

	public $awayTeamName;


...

What i think i need is something like this in search:





$activeDataProvider->getCriteria()->with = array(

	'homeCompTeam'=>array(

		'with'=>array('team', 'alias'=>'homeTeam')

	),

	'awayCompTeam'=>array(

		'with'=>array('team', 'alias'=>'awayTeam')

	),

);

$activeDataProvider->getCriteria()->compare('homeTeam.teamName',$this->homeTeamName, true );

$activeDataProvider->getCriteria()->compare('awayTeam.teamName',$this->awayTeamName, true );

	

$activeDataProvider->getSort()->attributes = array(

	'homeTeamName' => array(

		'asc' => 'homeTeam.teamName',

		'desc' => 'homeTeam.teamName DESC',

	),

	'awayTeamName' => array(

		'asc' => 'awayTeam.teamName',

		'desc' => 'awayTeam.teamName DESC',

	),

	'*',

);

Any pointers would be great!

Thanks all!

Ross

2

Hi Ross,

You can use the name of the relation as the alias:




	$activeDataProvider->getCriteria()->compare('homeCompTeam.teamName',$this->homeTeamName, true );

	$activeDataProvider->getCriteria()->compare('awayCompTeam.teamName',$this->awayTeamName, true );

	

	$activeDataProvider->getSort()->attributes = array(

		'homeTeamName' => array(

			'asc' => 'homeCompTeam.teamName',

			'desc' => 'homeCompTeam.teamName DESC',

		),

		'awayTeamName' => array(

			'asc' => 'awayCompTeam.teamName',

			'desc' => 'awayCompTeam.teamName DESC',

		),

		'*',

	);
3

Hi Softark,

Thanks for replying! Ive wrecked my laptop setup at the minute but will try as soon as i get it sorted and let you know.

Thanks again!

Ross

4

Works a treat Softark. Thanks for the help!