Case insensitive where clause in gql query for StringProperty

Question

Using the google appengine datastore, is there a way to perform a gql query that specifies a WHERE clause on a StringProperty datatype that is case insensitive? I am not always sure what case the value will be in. The docs specify that the where is case sensitive for my values, is there a way to make this insensitive?

for instance the db Model would be this:

from google.appengine.ext import db
class Product(db.Model):
    id = db.IntegerProperty()
    category = db.StringProperty()

and the data looks like this:

id         category
===================
1          cat1
2          cat2
3          Cat1
4          CAT1
5          CAT3
6          Cat4
7          CaT1
8          CAT5

i would like to say

gqlstring = "WHERE category = '{0}'".format('cat1')
returnvalue = Product.gql(gqlstring)

and have returnvalue contain

id         category
===================
1          cat1
3          Cat1
4          CAT1
7          CaT1

Answer 1

I don't think there is an operator like that in the datastore.

Do you control the input of the category data? If so, you should choose a canonical form to store it in (all lowercase or all uppercase). If you need to store the original case for some reason, then you could just store two columns - one with the original, one with the standardized one. That way you can do a normal WHERE clause.

Answer 2

The datastore doesn't support case insensitive comparisons, because you can't index queries that use them (barring an index that transforms values). The solution is to store a normalized version of your string in addition to the standard one, as Peter suggests. The property classes in the AETycoon library may prove helpful, in particular, DerivedProperty.