Appengine Django/GQL - Ensuring Uniqueness of Model Type on Edit - Not Equals Filters

January 25th, 2010 by Blaine Garrett

Edit 2009-02-02 - Added note about inequality query complexity. Spoiler - inequalities are 2 queries under the hood. In my further adventures in Google AppEngine land, I wanted to ensure uniqueness of an email field while still allowing the user to change their email address. Since, Google App Engine's BigTable doesn't allow you to custom define unique indexes, this poses a problem. Instead of relying on database defined indexes, you have to set up your own rules, which is a wee bit tricky for GAE beginners such as me. This can be done in general by overloading the put() method on an entity, but I wanted to be able to handle the errors nicely for the form UI. The old Django Method: [sourcecode language='python'] #account = account entity you are editing email = form.cleaned_data['email'].lower() users_with_this_email = User.objects.filter(email=email).exclude(pk=account.pk).count() if (users_with_this_email > 0): # do something... [/sourcecode] The new Google AppEngine Big Table GQL method: [sourcecode language='python'] #account = account entity you are editing ... email = form.cleaned_data['email'].lower() users_with_this_email = Account.all().filter('email = ' , email).filter('__key__ != ' , account.key()).count(1) if (users_with_this_email > 0): # do something... [/sourcecode] The key here is making sure some OTHER entity does not also have the value for their email field. In regular Django, an entity will evaluate to its primary key when used in a query and can be filtered on 'pk' . GQL, however, the primary key needs to be explicitly fetched via entity.key() and filtered on __key__ A couple other notes:

GQL does have an email property. I have not run tests yet to tell if this is case sensitive.
GQL does not allow case-insensitive searches like regular Django does. Thus, I am making sure all email addresses are lowercased when inserting and querying against the db. Even if this is not needed for EmailProperties, you will need it for ensuring the uniqueness of other types.
!= filters are actually ran as two queries - one where the != is replace with a less than inequality filter, and one where it is replaced with a greater than inequality filter. No word at the moment if this applies to __key__ searches.
A query with a != cannot have inequality filters because of the above rule nor other != filters.