Complex query with group by

[Petawa]

OK, I'm still on the ladder I'm trying to program.
I have one table with contents: WinnerID, LoserID, Date.
Whenever a game is posted a new row is added.

Now, I would like to have a query that shows the results like this:
Game stats for (Player)
Opponent | Number of losses vs. this opponent | Number of wins versus this opponent.
Opponent2 | Number of losses vs. this opponent | Number of wins versus this opponent.
Etc.

So, here's what I'm thinking has to be done (in rough code with stuff missing).
SELECT * where winnerid=(player) group by loserid
SELECT * where loserid=(player) group by winnerid.
Join these rows.
But I have no clue how to count how many games either query returned for each player (so I get get the number of losses/number of wins versus that opponent)

I can't think of how to clarify it more, but I can try if you still don't understand...

Thanks,
Peter

[sad69]

I think you're looking for something like this:

SELECT WinnerID, LoserID, count(LoserID) FROM games_table WHERE WinnerID=(player) GROUP BY LoserID;


Similarily:

SELECT WinnerID, LoserID, count(LoserID) FROM games_table WHERE LoserID=(player) GROUP BY WinnerID;


You should change the table name to the one you're using as well. Let me know how that works out for you.

Sadiq.

[Petawa]

Something like that, but can they be combined into one query?
I tried LEFT Join with those values, it didn't work so well.

I've give a little more detail into my situation.
Diagrams of Tables:
WinnerID | LoserID | (date)
1 | 2
2 | 1
3 | 2

Then another table:
MemberID | Name
1 | PlayerA
2 | PlayerB
3 | PlayerC

And another table with ID & stats that I'll left join.
But, I want to query the top table and left join the other two to come up with a end array like this:
Name | ID | [Total Wins against constant player] | [Total losses against said player]
I'd then have a form that lets you enter the name of the player you want to view. (See http://www.wbcelite.com/forums/?pg=ladder&act=prevgames&player=Scourgedquake).
And I want the query to return a row for all players that the static player has won or lost against, which I would then loop through to print each separately in the table.

Here's what I'm trying (and failing) right now:
SELECT w.winner, l.loser, count(w.winner) as wins, count(l.loser) as losses from gf_ladder_games w left join gf_ladder_games l on (l.winner=w.loser) where l.winner=$playerid2 group by l.loser, w.winner

It returns 0 results.
Thanks for your time,
Peter

[sad69]

We may want to wait for someone else's input, but I don't think you can do this all in one query. What version of MySQL are you using? It may be possible using a sub-query, like this:

SELECT w.winner, l.loser, wins, losses
FROM (
(SELECT winner, loser, count(loser) wins FROM gf_ladder_games WHERE loser=4 GROUP BY winner) w
RIGHT JOIN
(SELECT winner, loser, count(loser) losses FROM gf_ladder_games WHERE winner=4 GROUP BY loser) l
ON
w.winner=l.loser)


That's the best I can do as far as putting it into one query, but you'll need MySQL 4.1 or later.

Sadiq.

[sad69]

This one uses an if statement to condense the winner and loser into opponent:

SELECT if(w.winner=NULL, w.winner, l.loser) opponent, wins, losses
FROM (
(SELECT winner, loser, count(loser) wins FROM gf_ladder_games WHERE loser=4 GROUP BY winner) w
RIGHT JOIN
(SELECT winner, loser, count(loser) losses FROM gf_ladder_games WHERE winner=4 GROUP BY loser) l
ON
w.winner=l.loser)


And the 4 in red, you should change that to $playerid2 or whatever..

Sadiq.

[Petawa]

I tried this:
SELECT if(w.winner=NULL, w.winner, l.loser) opponent, wins, losses, s.member_id, s.field_10 as pts, s.field_11 as rank, s.field_13 as prevwin, m.name,
FROM ( (gf_pfields_content) s
left join (gf_members) m
on s.member_id=m.id
right join
(SELECT winner, sum(ptsgain) opppts, count(winner) wins FROM gf_ladder_games WHERE loser=1464 GROUP BY winner) w
on s.member_id=w.winner
RIGHT JOIN (SELECT loser, sum(ptsgain) playerpts, count(loser) losses FROM gf_ladder_games WHERE winner=1464 GROUP BY loser) l ON w.winner=l.loser )
where s.member_id!=1464 and s.field_11!=0

I get an error like this:
query failed : You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM ( (gf_pfields_content) s left join (gf_members) m on s.me

Not sure quite what I did wrong, I just added the other tables to your query..

Thanks again for the help.

[raf]

remove the comma after the m.name

if(w.winner=NULL, w.winner, l.loser) opponent
should probaly be
if(w.winner=NULL, w.winner, l.loser) as opponent

but that's an insane, non performant query. I don't see why you want to do this in one query.

[Petawa]

Would it be better to run the one query (get from gf_members, merge gf_pfields_content, then run individual queries for each result of that (~150 rows) to get the # of wins, losses & sum of pts field?

Or is there a third way of doing it?

Never mind the help with that one query...I asked tech for my version, its 4.0.20-standard.

So I still need another way to get all the above data in a while loop :(

[raf]

there are multple ways to do this.

having one query with then a nested query that is ran when you process the results from your initial query, is certainly not the way to go.

what the best sollution is, will depend on your actual situation:
- how performant does this query needs to be --> will it be ran frequently? should the result be returned quickly?
- will there be empty cell in your resulting table --> does every couple of players met at least once?
- how dynamic is the data --> is it truly historic (like reporting over games from which you periodicaly add results) or are there continuously comming in new results (what's the update/selection ratio?)

Returning such agregated data is not realy 'in line' with the usual selects that you need for a db-driven webapplcation, so you either need to optimize your dbdesign for this, or do some processing in the applicationlayour.

I'd probably choose the second option, running two query's like sadiq posted, end then dumping the reusults in two arrays with the openents ID as the keys in both arrays (or into one 2D array). From threr on, it's realy simple to build the table.

[Petawa]

The query is probably not going to be run all that much - maybe 20 times a day. Its a fairly obscure feature page to show detailed stats on games by each player. There are many empty cells - some people will only ever play one game, some people will go on a 20-game losing streak against only one or two players and quit. Some people will try to play everyone.

There will probably be 5 new games each day, and I have a cron script running every midnight which updates ranks and updates win percentages.

Could you explain this 2D table to me please? I understand its a join run on the PHP-side? This may very well be what I'm looking for. I know its an obscure query, but its useful to see how people gained their points. From this data, I can also run calculations and display the points to be rewarded for the next game between two players.

[raf]

normally, your cron should then compose the table.

this means that the results will not be realtime, but will aalwas reflect the situation as it was on day-1.
games from today would then not be included.

to make it reltime, you run the two selects (one with the count for the #wins and one for the #loses

When you then proces the 2 recordsets, you just ad them to an array like

$lostall = False;
$wonall = False;
$sql="SELECT LoserID, count(*) an numlost FROM games_table WHERE WinnerID=(player) GROUP BY LoserID";
$resultwins=mysql_query($sql) or die ('Dueryproblem geting lost stats');
if (mysql_num_rows($resultwins)>=1){
while($rowwins=mysql_fetch_array($resultwins)){
$arr_stats[$rowwins[LoserID]][loss]=$rowwins[numlost];
}
} else {
$lostall = True;
}
mysql_free_result($resultwins);

$sql="SELECT WinnerID, count(*) an numwon FROM games_table WHERE LoserID=(player) GROUP BY WinnerID";
$resultloss=mysql_query($sql) or die ('Dueryproblem geting win stats');
if (mysql_num_rows($resultloss)>=1){
while($rowlost=mysql_fetch_array($resultloss)){
$arr_stats[$rowlost[WinnerID]][won]=$rowlost[numwon];
}
} else {
$wonall = True;
}
mysql_free_result($resultloss);

echo ('Resultstable for (player)<br />
<table>
<tr><td>Oponent</td><td># as winner</td><td># as loser</td></tr>');
foreach($arr_stats as $oponent){
if ($wonall){
$won='0';
}else{
$won=$arr_stats[$oponent][won];
}
if ($lostall){
$loss='0';
}else{
$loss=$arr_stats[$oponent][loss];
}
echo('<tr><td>' . $oponent . '</td><td>' . $won . '</td><td>' . $loss . '</td>);
}
echo ('</table>');


I didn't debug it, but this should get you the general idea.

[Petawa]

It worked, thanks a lot :) Since the page is so intensive, I'm thinking of caching it and updating once a day. But wouldn't I have to then cache a diff page for every player? Maybe the first time its run each day, it queries and caches it. Each time it runs, it checks to see if there's a cache, uses it if there is or queries otherwise. Each day at midnight, all caches are deleted. Would this work?
Here's the page: http://www.wbcelite.com/forums/index.php?pg=ladder&act=prevgames&player=Petawa

Anyway, here's the final code (less DB connect).
$player=$_GET["player"];
$result = mysql_query("SELECT id,name from gf_members where name='$player'") or die('query failed : '.mysql_error().'<br>'.$query);
$row=mysql_fetch_array($result); //Make sure entered player exsists, get ID
$playerid2=$row["id"];
$player=$row["name"];
if(!$playerid2){
$content="<center>Sorry, this player does not exsist. Make sure you typed the member name correctly, <a href='http://www.wbcelite.com/forums/index.php?act=Members'>click here</a> for a list of all members.<br><form action='http://www.wbcelite.com/forums/index.php'><input type='hidden' name='pg' value='ladder'><input type='hidden' name='act' value='prevgames'>View stats for <input type='text' name='player' style='background-color:#4F0000;width:80px'> <input type='submit' value='Go' style='background-color:#4F0000;'></form>";
}else{
$playerstats=mysql_query("SELECT field_10 as pts, field_11 as rank, field_13 as prevwin from gf_pfields_content where member_id=$playerid2");
$playerdetails=mysql_fetch_array($playerstats); //Use ID to get base stats of player
$content="<center><font size=4><b>Game Stats for <a href='http://www.wbcelite.com/forums/?showuser=$playerid2'>$player</a> (#$playerdetails[rank])</b></font><br>The numbers in brackets are the total points won for those games.<br><form action='http://www.wbcelite.com/forums/index.php'><input type='hidden' name='pg' value='ladder'><input type='hidden' name='act' value='prevgames'>View stats for <input type='text' name='player' style='background-color:#4F0000;width:80px'> <input type='submit' value='Go' style='background-color:#4F0000;'></form><br><br><table align=center cellpadding=5 cellspacing=0 style='text-align:center;border: 1px solid #800000;font-size:12px;font-weight:bold;'><tr><td class='maintitle' width='140'>Opponent<br>Name</td><td class='maintitle' width='100'># Losses vs. $player</td><td class='maintitle' width='100'># Wins vs. $player</td><td class='maintitle' width='100'>$player's next win worth</td><td class='maintitle' width='100'>Opponent's next win worth</td></tr>"; //Setup table
$statsquery="SELECT s.member_id, s.field_10 as pts, s.field_11 as rank, s.field_13 as prevwin, m.name FROM gf_pfields_content s left join gf_members m on s.member_id=m.id where s.member_id!=$playerid2 and s.field_11!=0";
$resultstats=mysql_query($statsquery) or die('Cannot get base stats'); //Get base stats for each other player in ladder, put into array
while($rowstats=mysql_fetch_array($resultstats)){
$arr_stats[$rowstats[member_id]][pts]=$rowstats[pts];
$arr_stats[$rowstats[member_id]][rank]=$rowstats[rank];
$arr_stats[$rowstats[member_id]][prevwin]=$rowstats[prevwin];
$arr_stats[$rowstats[member_id]][name]=$rowstats[name];
$arr_stats[$rowstats[member_id]][id]=$rowstats[member_id];
}
mysql_free_result($resultstats);

$sql="SELECT loser, count(*) as numlost, sum(ptsgain) as plpts FROM gf_ladder_games WHERE winner=$playerid2 GROUP BY loser";
$resultwins=mysql_query($sql) or die ('Queryproblem geting lost stats');
while($rowwins=mysql_fetch_array($resultwins)){
$arr_stats[$rowwins[loser]][loss]=$rowwins[numlost]; //Get specific stats to # of games played, add to array
$arr_stats[$rowwins[loser]][plpts]=$rowwins[plpts];
}
mysql_free_result($resultwins);

$sql="SELECT winner, count(*) as numwon, sum(ptsgain) as oppts FROM gf_ladder_games WHERE loser=$playerid2 GROUP BY winner";
$resultloss=mysql_query($sql) or die ('Queryproblem geting win stats');
while($rowlost=mysql_fetch_array($resultloss)){
$arr_stats[$rowlost[winner]][won]=$rowlost[numwon];
$arr_stats[$rowlost[winner]][oppts]=$rowlost[oppts];
}
mysql_free_result($resultloss);
foreach($arr_stats as $opponent){
if(!$opponent[won]){$opponent[won]=0;}
if(!$opponent[loss]){$opponent[loss]=0;}
if(!$opponent[plpts]){$opponent[plpts]=0;}
if(!$opponent[oppts]){$opponent[oppts]=0;}
$nextwin=round(10*(($opponent[pts]*$opponent[prevwin])+1)/($opponent[loss]+5),1);
$nextloss=round(10*(($playerdetails[pts]*$playerdetails[prevwin])+1)/($opponent[won]+5),1);
$content=$content."<tr><td><a href='http://www.wbcelite.com/forums/?showuser=$opponent[id]'>$opponent[name]</a> (#$opponent[rank])</td><td>$opponent[loss] <span style='font-weight:normal;'>($opponent[plpts])</span></td><td>$opponent[won] <span style='font-weight:normal;'>($opponent[oppts])</span></td><td>$nextwin</td><td>$nextloss</td></tr>";
}
$content=$content."<tr><td class='maintitle' colspan=5 align=right></td></tr></table>";

[raf]

It worked, thanks a lot :) Since the page is so intensive, I'm thinking of caching it and updating once a day.

No I don't think that would be a good idea.

The processing of the recordsets (looping through them, building the arrays, looping through the arrays to build the table) probably don't take much time.
You can try it by timing it, but i'm fairly sure it wount take 0.1 second to proces the recordsets. So caching wount improve responsetimes significantly (if at all!!).

There is till some room for improvement though:
- Just make sure that you index gf_ladder_games.winner, gf_ladder_games.looser and gf_ladder_games.ptsgain + the joining fields from your stats-query.
<edit>Make sure that these columns are numericals --> foreign key of the members table instead of the actual names, since i looked at your side and i see you pass on the name there. Should be the membersID.</edit>
- I didn't take a close look at the query but if you can, try to avoid using an outerjoin like you now have, and if at all possible, try to avoid the stats-query at all, by joining on the count querys.

- If that is not an option, then i would first run the won and lost query. You can then add the member_id's of the winners and losers to an array and build a collection with it to then use inside the stats query. Like

while($rowwins=mysql_fetch_array($resultwins)){
$arr_oponents[]=$rowwins[loser];
$arr_stats[$rowwins[loser]][loss]=$rowwins[numlost];
...
}
...

while($rowlost=mysql_fetch_array($resultloss)){
$arr_oponents[]=$rowwins[winner];
$arr_stats[$rowlost[winner]][won]=$rowlost[numwon];
...
}
$membercollection=implode(', ', array_unique($arr_oponents));

$statsquery="SELECT s.member_id, s.field_10 as pts, s.field_11 as rank, s.field_13 as prevwin, m.name FROM gf_pfields_content s INNER JOIN gf_members m on s.member_id=m.id where s.member_id In (". $membercollection .");

i made it an inner join (don't quite see why it should be an outer join, but i might be wrong since i don't knwo the desing.

This will reduce the generated recordset + allows you to reduce the number of returned oponts. You could for instance only rturns the 5 biggest winers and loosers, so then this query would only grab the data for these 10 and not for your complete membertable

[Petawa]

All joins are done by MemberID. The first query takes member name (from form), and finds appropriate ID to use for furthur queries.

I will run the stats query second, only for players in which games were returned. It can't be avoided, as I want stats for all returned players (not just the winners or losers, both).

I'll also use inner-joins (I didn't know the diff before), and index the main keys.

Thanks for all the help!

Just one more quick question while you're on the thread.
Is there a way to "order by" arrays? IE-order the "rows" by wins/losses desc or asc, etc.

Also, is there a MODE (find most occuring) select for MySQL? Whenever a game is submitted, the player also submits the race used in that game. I want to find this race and print something like
"Petawa using orcs most of the time (78%)
Is that possible? Thanks so much, everything works great!