Filter and sort music info on Google App Engine

Question

I've enjoyed building out a couple simple applications on the GAE, but now I'm stumped about how to architect a music collection organizer on the app engine. In brief, I can't figure out how to filter on multiple properties while sorting on another.

Let's assume the core model is an Album that contains several properties, including:

• Title
• Artist
• Label
• Publication Year
• Genre
• Length
• List of track names
• List of moods
• Datetime of insertion into database

Let's also assume that I would like to filter the entire collection using those properties, and then sorting the results by one of:

• Publication year
• Length of album
• Artist name
• When the info was added into the database

I don't know how to do this without running into the exploding index conundrum. Specifically, I'd love to do something like:

Albums.all().filter('publication_year <', 1980).order('artist_name')

I know that's not possible, but what's the workaround?

This seems like a fairly general type of application. The music albums could be restaurants, bottles of wine, or hotels. I have a collection of items with descriptive properties that I'd like to filter and sort.

Is there a best practice data model design that I'm overlooking? Any advice?

Answer 1

As you say, you can't have an inequality condition on one field and an order by another (or inequalities on two fields, etc, etc). The workaround is simply to use the "best" inequality condition to get data in memory (where "best" means the one that's expected to yield the least data) and then further refine it and order it by Python code in your application.

Python's list comprehensions (and other forms of loops &c), list's sort method and the sorted built-in function, the itertools module in the standard library, and so on, all help a lot to make these kinds of tasks quite simple to perform in Python itself.

Answer 2

There's a couple of options here: You can filter as best as possible, then sort the results in memory, as Alex suggests, or you can rework your data structures for equality filters instead of inequality filters.

For example, assuming you only want to filter by decade, you can add a field encoding the decade in which the song was recorded. To find everything before or after a decade, do an IN query for the decades you want to span. This will require one underlying query per decade included, but if the number of records is large, this can still be cheaper than fetching all the results and sorting them in memory.

Answer 3

Since storage is cheap, you could create your own ListProperty based indexfiles with key_names that reflect the sort criteria.

class album_pubyear_List(db.Model):
    words = db.StringListProperty()

class album_length_List(db.Model):
    words = db.StringListProperty()

class album_artist_List(db.Model):
    words = db.StringListProperty()

class Album(db.Model):
    blah...

    def save(self):
        super(Album, self).save()

        # you could do this at save time or batch it and do
        # it with a cronjob or taskqueue

        words = []

        for field in ["title", "artist", "label", "genre", ...]:
            words.append("%s:%s" %(field, getattr(self, field)))

        word_records = []
        now = repr(time.time())
        word_records.append(album_pubyear_List(parent=self, key_name="%s_%s" %(self.pubyear, now)), words=words)
        word_records.append(album_length_List(parent=self, key_name="%s_%s" %(self.album_length, now)), words=words)
        word_records.append(album_artist_List(parent=self, key_name="%s_%s" %(self.artist_name, now)), words=words)
        db.put(word_records)

Now when it's time to search you create an appropriate WHERE clause and call the appropriate model

where = "WHERE words = " + "%s:%s" %(field-a, value-a) + " AND " + "%s:%s" %(field-b, value-b) etc.
aModel = "album_pubyear_List" # or anyone of the other key_name sorted wordlist models

indexes = db.GqlQuery("""SELECT __key__ from %s %s""" %(aModel, where))
keys = [k.parent() for k in indexes[offset:numresults+1]] # +1 for pagination
object_list = db.get(keys) # returns a sorted by key_name list of Albums