How to create autocomplete with GAE?

Question

I use jQuery UI autocomplete widget. Also I have GAE datastore:

class Person(db.Model):
    # key_name contains person id in format 'lastname-firstname-middlename-counter',
    # counter and leading dash are omitted, if counter=0
    first_name = db.StringProperty()
    last_name = db.StringProperty()
    middle_name = db.StringProperty()

How can I search the person in the autocomplete widget, when user can input there surname, first name and/or middle name?

So, I am getting user input string as self.request.get('term'). How should I search for the same in my datastore (since I need to look at each field and probably for combined value of 3 fields)? How to optimize such query? I am also not clear what should be the reply format. jQuery doc says:

A data source can be:

an Array with local data
a String, specifying a URL
a Callback

The local data can be a simple Array of Strings, or it contains Objects for each item in the array, with either a label or value property or both.

Answer 1

There are a few neat tricks here. Consider this augmented model:

class Person(db.Model):
  first_name = db.StringProperty()
  last_name = db.StringProperty()
  middle_name = db.StringProperty()
  names_lower = db.StringListProperty()

You'll need to keep names_lower in sync with the real fields, e.g.:

p.names_lower = [p.first_name.lower(), p.last_name.lower(),
                 p.middle_name.lower()]

You can do this more elegantly with a DerivedProperty.

And now, your query:

term = self.request.get('term').lower()
query = Person.all()
query.filter('names_lower >=', term)
query.filter('names_lower <=', unicode(term) + u"\ufffd")

This gives you:

• Matching on all 3 properties with one index
• Case insensitive matches
• Wildcard suffix matches

So a query for "smi" will return any person with any name starting with "smi" in any case.

Copying lower-cased names to a ListProperty enables case-insensitive matching, and allows us to search all 3 fields with one query. "\ufffd" is the highest possible unicode character, so it's the upper limit for our substring match. If for some reason you want an exact match, filter for 'names_lower =', term instead.

Edit:

How should I search for the same in my datastore (since I need to look at each field and probably for combined value of 3 fields)? How to optimize such query?

This is already accounted for in the original solution. By taking the 3 fields and copying them to a single ListProperty, we're essentially creating a single index with multiple entries per person. If we have a person named Bob J Smith, he'll have 3 hits in our index:

• names_lower = bob
• names_lower = j
• names_lower = smith

This eliminates the need to run distinct queries on each field.

I am also not clear what should be the reply format.

Read the docs carefully. Formatting output for jQuery should be pretty straightforward. Your data source will be a string specifying a URL, and you'll want to format the response as JSON.

Answer 2

Basically agreeing on everything Drew wrote I post a link to my blog with rather elaborate example for auto-completing selecting keywords when searching for information in the datastore.

All done in GAE with Python and using YUI3 instead of jQuery (plugging in jQuery or any other library instead would be trivial).

Shortly, the idea is that datastore contains set of documents that are indexed using keywords (using Relation Index Entity). And when user enters words to search for, the system autocompletes them with the keywords from those documents.