Relational Active Record

Yii 1.1 General Discussion
1

Hi,

i have a table Fighters (where i keep curent weight and league info)

and a table FightersUpdates (where i track all the changes about weight and league)

in AddMatch when i choose home fighter it checks in what league is at matchdate and populate away fighter’s list only with fighters from that league at that time.

and i don’t know how to do this.




public function relations() {

	return array(

		'fightersUpds' => array(self::HAS_MANY, 'FightersUpd', 'id_fighter'),

	);

}







public function actionDynamicFighters()

	{

		// Find fighter's league at matchdate

		$home=FightersUpd::model()->findByAttributes(array(

						'id_fighter'=>(int) $_POST['Matches']['id_home']

						),

						'change_date<=:change_date',

						array(':change_date'=>$_POST['Matches']['date']

						));

		// If there is no result

		if(!$home)

		{

			// get current fighter's league

			$home=Fighters::model()->findByPk((int) $_POST['Matches']['id_home']);

		}

		

		// Find all away fighters in home's league at matchday

		$away=Fighters::model()->with(array('fightersUpds'=>array(

    					'condition'=>'change_date<=:change_date AND id_fighter!=:id_fighter',

    					'params'=>array(

							':change_date'=>$_POST['Matches']['date'],

							':id_fighter'=>(int) $_POST['Matches']['id_home']

    				))->findAll(array(

						'order'=>'name',

						'condition'=>'id_league=:id_league AND id!=:id',

						'params'=>array(

							':id_league'=>(int) $home->id_league,

							':id'=>(int) $_POST['Matches']['id_home']

					)));

		

		$away=CHtml::listData($away,'id','name');

		echo '<option value="">= Select Away Fighter =</option>';

		foreach($away as $value=>$name)

		{

			echo CHtml::tag('option',

				array('value'=>$value),CHtml::encode($name),true);

		}

 

		/* THIS ONE IS WORKING BUT IT RETURNS FIGHTERS WITH PRESENT ID_LEAGUE OF COURSE

		$away=Fighters::model()->findAll(array(

						'order'=>'name',

						'condition'=>'id_league=:id_league AND id!=:id',

						'params'=>array(

							':id_league'=>(int) $home->id_league,

							':id'=>(int) $_POST['Matches']['id_home']

					)));

		

		$away=CHtml::listData($away,'id','name');

		echo '<option value="">= Select Away Fighter =</option>';

		foreach($away as $value=>$name)

		{

			echo CHtml::tag('option',

				array('value'=>$value),CHtml::encode($name),true);

		}

		*/

	}

Please help me to do the SQL request proper.

Thank you

2

i think i managed it somehow but how do i limit search to first record ?




$away=FightersUpd::model()->with('idFighter')->findAll(array(

			'order'=>'idFighter.name',

			'condition'=>'t.id_league=:id_league

						AND t.id_fighter!=:id_fighter

						AND t.change_date<=:change_date',

			'params'=>array(

				':id_league'=>(int) $home->id_league,

				':id_fighter'=>(int) $_POST['Matches']['id_home'],

				':change_date'=>$_POST['Matches']['date']

		)));
3

I thought you wanted a list of related records to populate your fighters list but if you only want 1 record to be returned with your sample code, then just use find()

4

you thought well, i need a list but because is a HAS_MANY relation it finds me many record of fighter x in league y with change_date<=:change_date.

if the match is in 2014 than all the previous records (2013, 2012 etc) are <=:change_date and i need only the first one from each fighter.

if i use find() it won’t be a list, just 1 record.

5

Your code is a bit confusing without seeing the whole picture (database, models, etc).

I think you can try creating another relationship but this time use HAS_ONE. Use that inside with() instead in your original code.




// Find all away fighters in home's league at matchday

// fightersUpd uses HAS_ONE

$away = Fighters::model()->with(array(

	'fightersUpd' => array(

		'condition' => 'change_date <= :change_date',

		'params' => array(

			':change_date' => $_POST['Matches']['date'],

		)

	)

))->findAll(array(

	'order' => 'name',

	'condition' => 'id_league = :id_league AND id <> :id',

	'params' => array(

		':id_league' => (int) $home->id_league,

		':id' => (int) $_POST['Matches']['id_home']

)));
6

Thank you but still doesn’t work. it doesn’t stop untill it reaches the last value meeting the criteria … :(

7

more details:

in 2012.01.01 fighter 1 was on league middleweight

in 2013.01.01 fighter 1 was on league heavyweight

in 2014.01.01 fighter 1 was on league middleweight

when i add a match on 2014.03.03 it says the fighter 1 is on middleweight which is correct but from 2012.01.01 which is wrong

when i add a match on 2013.03.03 it says the fighter 1 is on middleweight which is wrong but from 2012.01.01 which is also wrong

i really don’t know how to make it stop after it finds a match.

8

I solved the problem adding new column (stop_date) into the table.

is this really the only sollution ???




$away = Fighters::model()->with(array(

	'fightersUpds' => array(

		'alias'	=> 'f',

		'select'=> 'id',

		'condition' => 'f.change_date <= :change_date

					AND f.stop_date > :stop_date   // NEW COLUMN

					AND f.id_league = :id_league

              				AND f.id_fighter != :id_fighter',

		'params' => array(

					':change_date' => $_POST['Matches']['date'],

					':stop_date' => $_POST['Matches']['date'],

					':id_league' => (int) $home->id_league,

					':id_fighter' => (int) $_POST['Matches']['id_home']

		))))->findAll(array(

			'select'=> 'id,name,surname,id_league',

			'order'	=> 'name',

));