Display a ranking grid for game : optimization of left outer join and find a player

Question

Hello,

I want to do a ranking grid.

I have a table with different values indexed by a key:

Table SimpleValue : key varchar, value int, playerId int

I have a player which have several SimpleValue.

Table Player: id int, nickname varchar

Now imagine these records:

SimpleValue:

Key      value     playerId
for      1         1
int      2         1
agi      2         1
lvl      5         1

for      6         2
int      3         2
agi      1         2
lvl      4         2

Player:

id     nickname
1      Bob
2      John

I want to display a rank of these players on various SimpleValue. Something like:

nickname      for       lvl     
Bob           1         5       
John          6         4  

For the moment I generate an sql query based on which SimpleValue key you want to display and on which SimpleValue key you want to order players.

eg: I want to display 'lvl' and 'for' of each player and order them on the 'lvl'

The generated query is:

SELECT p.nickname as nickname, v1.value as lvl, v2.value as for
FROM Player p
LEFT OUTER JOIN SimpleValue v1 ON p.id=v1.playerId and v1.key = 'lvl'
LEFT OUTER JOIN SimpleValue v2 ON p.id=v2.playerId and v2.key = 'for'
ORDER BY v1.value

This query runs perfectly. BUT if I want to display 10 different values, it generates 10 'left outer join'. Is there a way to simplify this query ?

I've got a second question: Is there a way to display a portion of this ranking. Imagine I've 1000 players and I want to display TOP 10, I use the LIMIT keyword. Now I want to display the rank of the player Bob which is 326/1000 and I want to display 5 rank player above and below (so from 321 to 331 position). How can I achieve it ?

thanks.

Answer 1

This is called a "Crosstab query". You can use conditional sum statements like this page

To get:

SELECT p.nickname as nickname
, SUM(IF(key = "for", value,0)) AS `for`
, SUM(IF(key = "int", value,0)) AS `int`
, SUM(IF(key = "agi", value,0)) AS `agi`
, SUM(IF(key = "lvl", value,0)) AS `lvl`
FROM Player p
LEFT OUTER JOIN SimpleValue v1 ON p.id=v1.playerId
GROUP By p.nickname
ORDER BY v1.value