Date Range Condition On Relation With Through

Yii 1.1 General Discussion
1

Hey,

I’ve got 3 tables - Team, Player, and Goal. A Team has many players, and a player can have many goals. I would like to query all the goals scored on a team for a specific date range.

I currently have a relation on Team CActiveRecord like the following:


class Team extends CActiveRecord

{

	...

	public function relations()

	{

		return array(

			'players' => array(self::HAS_MANY, 'Player', 'teamID'),

			'goals'=>array(

                                  self::HAS_MANY,'Goal',array('playerID'=>'playerID'),'through'=>'players'

                        ),

		);

	}

}

This works great for retrieving all the Goals for a specific team, but I’m not sure how I would query all the Goals on a Team for the past week.

FYI: Goal has a DATETIME column ‘time’ in which is set on the time the Goal occurred.

I’m assuming this is not possible using a Relation, but rather I’d have to use Team::model->findAll(…Condition…); But I don’t know how I’d properly join to the Goal table (like the ‘through’ command in the relation).

Of course I could do this all with Raw SQL, but I want to know the best ‘Yii’ way.

Thanks in advance!

2

Maybe helpful.

in your controller:




$model = Team::model()->with(array(

  	'goals'=>array(

          	'condition'=>'Goal.time > 2013/11/25 and Goal.time<2013/12/02'

    	),

         	))->findAll();
3

If you set a condition like that that will filter out teams/players with no goals in this range. You need to add the condition in the ‘on’ property, like so:




$model = Team::model()->with(array(

        'goals'=>array(

                'on'=>'Goal.time BETWEEN :from AND :to'

                'params'=>array(':from'=>'2013-11-25', ':to'=>'2013-12-02'),

        ),

))->findAll();

BETWEEN is actually >= and <=.

That is valid when you don’t enable ‘through’ or explicitly use ‘inner join’ instead of the default ‘left join’.

4

Awesome! NineInchNick’s solution seems to work better for me. Here’s what I’m using now…


$teams = Team::model()->with(array(

        'goals'=>array(

                'on'=>'goals.time BETWEEN :from AND :to',

                'params'=>array(':from'=>'2013-11-25', ':to'=>'2013-12-02'),

        ),

	))->findAllByAttributes(array('ownerUserID'=>$user->userID));   //Make sure current user "owns" these teams

$players = $model[0]->players;

$goals = $model[0]->goals;

Thanks for your help guys!