How to do AVG and SUM in Google App Engine Data Store

People who are used to relational databases, which is pretty much every gosh-darned web developer out there, will run into pretty much the same obstacles with the app engine datastore - one of them is How the heck do I do SUM or AVG?. Yeah, due to how the Data Store works - you cannot do any kind of aggregate query. Instead, you have to re-calculate the totals at write time and keep them in a Counter instead. Like this:

models.py
from appengine_django.models import BaseModel from google.appengine.ext import db from google.appengine.ext.db import NotSavedError from decimal import Decimal, getcontext class Callable: # this is a class that wraps a method so that we can call it like a static method, directly on the Class, instead of on an instance. def __init__(self, anycallable): self.__call__ = anycallable class GlobalCounter(BaseModel): name = db.StringProperty(required=True) value = db.IntegerProperty(required=True) class Rating(BaseModel): user = db.UserProperty(required=True) rating = db.IntegerProperty(required=True) # hide this oldRating = 0 def set_rating(self, val): self.oldRating = self.rating self.rating = val def put(self): self.save() def save(self): # get the global counters from the datastore. votes = GlobalCounter.all().filter("name = ", "numberOfVotes").get() total = GlobalCounter.all().filter("name = ", "totalAmountOfVotes").get() if (votes == None): votes = GlobalCounter(name = "numberOfVotes", value = 0) votes.save() if (total == None): total = GlobalCounter(name = "totalAmountOfVotes", value = 0) votes.save() # check wheter the rating is a new rating, or if the user is changing his vote. isSaved = False try: self.key() isSaved = True except NotSavedError: isSaved = False if (isSaved): # the user is re-saving (and possibly changing) his vote - subtract his old vote from the total, and add his new vote total.value = total.value - self.oldRating + self.rating else: votes.value += 1 total.value += self.rating votes.save() total.save() BaseModel.save(self) def ___getAverage___(): votesCounter = GlobalCounter.all().filter("name = ", "numberOfVotes").get() totalCounter = GlobalCounter.all().filter("name = ", "totalAmountOfVotes").get() getcontext().prec = 3 return Decimal(str(totalCounter.value)) / Decimal(str(votesCounter.value)) average = Callable(___getAverage___) # make average() behave like a static variable def ___getTotal___(): totalCounter = GlobalCounter.all().filter("name = ", "totalAmountOfVotes").get() return totalCounter.value sum = Callable(___getTotal___) # make average() behave like a static variable

And you use it like this...

tests.py
import os from decimal import Decimal, getcontext #import the stubs, i.e. the fake datastore, user and mail service and urlfetch from google.appengine.api import apiproxy_stub_map from google.appengine.api import datastore_file_stub from google.appengine.api import mail_stub from google.appengine.api import urlfetch_stub from google.appengine.api import user_service_stub from google.appengine.api import users # import the Django Forms replacement provided by the App Engine SDK, so that Django Forms get Data Store model support. from google.appengine.ext.db.djangoforms import ModelForm # I use Python Mocker to create mock objects: http://labix.org/mocker import mocker from mocker import MockerTestCase # importing our Model from avgtest.models import Rating from avgtest.models import GlobalCounter class TestStart(MockerTestCase): def setUp(self): # Start with a fresh api proxy. apiproxy_stub_map.apiproxy = apiproxy_stub_map.APIProxyStubMap() # Use a fresh stub datastore. # From this point on in the tests, all calls to the Data Store, such as get and put,, # will be to the temporary, in-memory datastore stub. stub = datastore_file_stub.DatastoreFileStub(u'myTemporaryDataStorage', '/dev/null', '/dev/null') apiproxy_stub_map.apiproxy.RegisterStub('datastore_v3', stub) # Use a fresh stub UserService. apiproxy_stub_map.apiproxy.RegisterStub('user', user_service_stub.UserServiceStub()) os.environ['AUTH_DOMAIN'] = 'gmail.com' os.environ['USER_EMAIL'] = 'myself@appengineguy.com' # set to '' for no logged in user os.environ['SERVER_NAME'] = 'fakeserver.com' os.environ['SERVER_PORT'] = '9999' # Use a fresh urlfetch stub. apiproxy_stub_map.apiproxy.RegisterStub('urlfetch', urlfetch_stub.URLFetchServiceStub()) # Use a fresh mail stub. apiproxy_stub_map.apiproxy.RegisterStub('mail', mail_stub.MailServiceStub()) # Mock all calls to HttpResponseRedirect self.HttpResponseRedirect = self.mocker.replace("django.http.HttpResponseRedirect") # Mock all calls to the render_to_response shortcut self.render_to_response = self.mocker.replace("django.shortcuts.render_to_response") def testAverage(self): rA = Rating(user = users.get_current_user(), rating = 1) rA.save() self.assertEquals(1, Rating.average()) rB = Rating(user = users.get_current_user(), rating = 4) rB.put() self.assertEquals(Decimal("2.5"), Rating.average()) rC = Rating(user = users.get_current_user(), rating = 2) rC.save() self.assertEquals(Decimal("2.33"), Rating.average()) def testSum(self): rA = Rating(user = users.get_current_user(), rating = 2) rA.save() rB = Rating(user = users.get_current_user(), rating = 4) rB.put() rC = Rating(user = users.get_current_user(), rating = 3) rC.save() self.assertEquals(9, Rating.sum()) def testAverageChangeVote(self): rA = Rating(user = users.get_current_user(), rating = 1) rA.save() self.assertEquals(1, Rating.average()) rB = Rating(user = users.get_current_user(), rating = 4) rB.put() self.assertEquals(Decimal("2.5"), Rating.average()) rC = Rating(user = users.get_current_user(), rating = 2) rC.save() self.assertEquals(Decimal("2.33"), Rating.average()) rC.set_rating(3) # the C user changes his mind to 3 instead. rC.save() self.assertEquals(Decimal("2.67"), Rating.average())

Performance note: It is a slightly bad idea to do counters like I have done above, but in my defence, I have done it for simplicity. In practice, it's important that you shard your counters.

As always, comments and questions are always welcome!