leaderboard: delegate calculations and sorting to database

[auscompgeek]

Currently, the leaderboard won't work very well if we end up with a lot of users, since all the processing is done on the Python side rather than the database side.

Here's a couple of ways this could work:

SELECT user_id, SUM(num_correct) * 1.0 / SUM(num_answered) AS total_ratio
FROM scores
GROUP BY user_id
ORDER BY total_ratio DESC
LIMIT 10;

SELECT username, SUM(num_correct) * 1.0 / SUM(num_answered) AS total_ratio
FROM scores
NATURAL JOIN users
GROUP BY user_id
ORDER BY total_ratio DESC
LIMIT 10;

The first query would be easiest to get working with the database API. I'm not sure how we could get joins working nicely with Model._query().

NB: Right now, I have an uncommitted version of the database API where Model._query() takes a few more optional arguments: _group_by: str, _sort_by: str, limit: int.

[auscompgeek]

Maybe I'll add in a _join: str or something to Model._query() which only does NATURAL JOIN. Hmm.

[auscompgeek]

Hmm. This is what the query currently looks like:

SELECT user_id, username, SUM(num_correct) AS total_correct, SUM(num_answered) AS total_answered, total_correct * 1.0 / total_answered AS total_ratio
FROM scores NATURAL JOIN users
GROUP BY user_id
ORDER BY total_ratio DESC
LIMIT 10;

Except, uhh, apparently I can't use custom columns I define in later custom columns?

So, one option I see to solve this is to not return a total_ratio column. I'm not sure whether this is the best idea though. There could be other solutions though, and I'd like to hear people's thoughts on this.