How to get the distinct value of one of my models in Google App Engine

Question

I have a model, below, and I would like to get all the distinct area values. The SQL equivalent is select distinct area from tutorials

class Tutorials(db.Model):  
    path = db.StringProperty()
    area = db.StringProperty()
    sub_area = db.StringProperty()
    title = db.StringProperty()
    content = db.BlobProperty()
    rating = db.RatingProperty()
    publishedDate = db.DateTimeProperty()
    published = db.BooleanProperty()

I know that in Python I can do

    a = ['google.com', 'livejournal.com', 'livejournal.com', 'google.com', 'stackoverflow.com']
 b = set(a)
    b
    >>> set(['livejournal.com', 'google.com', 'stackoverflow.com'])

But that would require me moving the area items out of the query into another list and then running set against the list (sounds very inefficient) and if I have a distinct item that is in position 1001 in the datastore I wouldnt see it because of the fetch limit of 1000.

I would like to get all the distinct values of area in my datastore to dump it to the screen as links.

Answer 1

Datastore cannot do this for you in a single query. A datastore request always returns a consecutive block of results from an index, and an index always consists of all the entities of a given type, sorted according to whatever orders are specified. There's no way for the query to skip items just because one field has duplicate values.

One option is to restructure your data. For example introduce a new entity type representing an "area". On adding a Tutorial you create the corresponding "area" if it doesn't already exist, and on deleting a Tutoral delete the corresponding "area" if no Tutorials remain with the same "area". If each area stored a count of Tutorials in that area, this might not be too onerous (although keeping things consistent with transactions etc would actually be quite fiddly). I expect that the entity's key could be based on the area string itself, meaning that you can always do key lookups rather than queries to get area entities.

Another option is to use a queued task or cron job to periodically create a list of all areas, accumulating it over multiple requests if need be, and put the results either in the datastore or in memcache. That would of course mean the list of areas might be temporarily out of date at times (or if there are constant changes, it might never be entirely in date), which may or may not be acceptable to you.

Finally, if there are likely to be very few areas compared with tutorials, you could do it on the fly by requesting the first Tutorial (sorted by area), then requesting the first Tutorial whose area is greater than the area of the first, and so on. But this requires one request per distinct area, so is unlikely to be fast.