Im creating a football/soccer website and want to show on a page player stats (via CDetailView) as well as match stats (via CGridView) in which the player has played in e.g.
Details: Name: Joe Bloggs DOB: 1/1/1 Played: 12 Scored: 1 Assists: 1 Matches: Team | Score | Opp | Position | Scored | Assists | Team A | 2 - 0 | Team B | Goalkeeper | 1 | 0 | Team A | 5 - 2 | Team C | Goalkeeper | 2 | 1 |
I have a view which combines the information to display the Matches info which contains the player info per row i.e.
row 1: PlayerName, DOB, Team A, 2 - 0, Team B, Goalkeeper, 1, 0
row 2: PlayerName, DOB, Team A, 5 - 2, Team C, Goalkeeper, 2, 1
What i want to do is populate the Scored/Assists as the sum of the view scored where the playerId = the curently being viewed player id. Should this be a stat query? If so how should it be defined on itself? The View has a number of critical fields linking the scored etc namely matchPlayerId, memberId and matchId. Primary key of the view is the matchPlayerId as obviusly the view contains multiple memberId’s and match Ids for each match/position player.
So the query really needs to be
SELECT SUM(scored) AS `s` FROM `view` `t` WHERE (`t`.`memberId`=:memberIdBeingViewes);
'scoredCount' => array(self::STAT, 'VwMemberStats', 'matchPlayerId', 'select'=>'SUM(scored)', 'where'=>'memberId='.$this->memberId),
but member Id isnt set when the relations are created.
Any ideas? Its my first time sing STAT queries and im not sure if they are the best option here?