Alternative strategy to query aggregation ("group by") in google app engine datastore

Question

App Engine Datastore cannot be queried for an aggregate result.

Example: I have an entity called "Post" with the following fields:

Key id, String nickname, String postText, int score

I have many different nicknames and many posts by each nickname in my datastore.

If I want a leader board of the top ten nicknames of total scores, I would typically have sql as follows:

select nickname, sum(score) as sumscore
from Post 
group by nickname 
order by sumscore 
limit 10

This type of query is not possible in google app engine datastore java api (jdo or jpa).

What are alternative strategies that I could use to achieve a similar result?

Crudely and brutely, I could load every Post entity and compute the aggregation fully in my application code. This is obviously not efficient on large datasets.

What other strategies can I employ?

Answer 1

Create a Nickname model, and each time you add a new Post, retrieve the corresponding Nickname and increase a stored score sum there. Essentially, do the computation at insert/update-time, not query-time.