Stat Relation or scope or what!?

Yii 1.1 General Discussion
1

Hi All,

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

etc

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);

Ive got


'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?

Many Thanks

Ross

2

What would the corresponding SQL be?

Even so in an aggregation SUM, COUNT etc you would use the HAVING member_id = …

But still I am more confused why would you need this "condition" (erase the "where") ?

Unfortunately I cannot help you much without the corresponding part of your db diagram

3

Hi Pligor,

Attached is my schema diagram. I think im just going to use Db views for it as its less complicated than funky criterias in my model. I guess it comes down to theirs more than one way to acheive the same thing

[list=1]

[*]DB View - Easiest and quickest

[*]Custom Criteria

[*]Foreign Keys and Stat queries

[/list]

I figure a view will be quicker in the long run as well but if anyone has better ideas let me know!

Thanks

Ross

4 

SELECT SUM(scored) AS `s` FROM

`view` `t` WHERE (`t`.`memberId`=:memberIdBeingViewes);

Ive got


'scoredCount' => array(self::STAT, 'VwMemberStats', 'matchPlayerId', 'select'=>'SUM(scored)', 'where'=>'memberId='.$this->memberId),

[indent]

I believe you must use ‘condition’ when using relations, NOT ‘where’. Condition corresponds to a where clause, so you should have something more like this:




'ticketHours' => array(self::STAT,'TicketEmployeeHours','ticketID','select'=>"SUM(hours)"),

Yours should look like this:


'scoredCount' => array(self::STAT, 'VwMemberStats', 'matchPlayerId', 'select'=>'SUM(scored)', 'condition'=>"memberId=$this->memberId"),

You might need to specify a table alias when referring to memberId above, just beware of that (in which case you could use $t = $this->getTableAlias(false,false); and specify the condition as "$t.memberId=$this->memberId").

You can use it my specifying $model->scoredCount. You might consider eagerly loading it if you want to display it for more than a few records at a time.

I know this is late, but it might help somebody.

[/indent]