How to filter rows with null refrences in Google a

Question

I have a Model UnitPattern, which reference another Model UnitPatternSet

e.g.

class UnitPattern(db.Model):
    unit_pattern_set = db.ReferenceProperty(UnitPatternSet)

in my view I want to display all UnitPatterns having unit_pattern_set refrences as None, but query UnitPattern.all().filter("unit_pattern_set =", None) returns nothing, though I have total 5 UnitPatterns, out of which 2 have 'unit_pattern_set' set and 3 doesn't have

e.g.

print 'Total',UnitPattern.all().count()
print 'ref set',UnitPattern.all().filter("unit_pattern_set !=", None).count()
print 'ref not set',UnitPattern.all().filter("unit_pattern_set =", None).count()

outputs:

Total 5
ref set 2
ref not set 0

Shouldn't sum of query 2 and 3 be equal to query 1 ?

Reason seems to be that I added reference property unit_pattern_set later on, and these UnitPattern objects existed before that, but then how can I filter such entities?

Answer 1

This is described succinctly in the docs:

An index only contains entities that have every property referred to by the index. If an entity does not have a property referred to by an index, the entity will not appear in the index, and will never be a result for the query that uses the index.

Note that the App Engine datastore makes a distinction between an entity that does not possess a property and an entity that possesses the property with a null value (None). If you want every entity of a kind to be a potential result for a query, you can use a data model that assigns a default value (such as None) to properties used by query filters.

In your case, you have 3 entities that don't have the unit_pattern_set property set at all (because that property wasn't defined in the Model at the time those entities were created) - therefore those properties doesn't exist in the database representation of that entity, therefore that entity does not appear in the index of that property for that kind of entity.

Dan Sanderson's book Programming Google App Engine explains this in great detail on ~page 150 (unfortunately not available in the Google Books preview)

To fix the models you already have, you'll have to iterate over a query on UnitPattern (I've not tested the following code, please check it before you run it on your live data):

patterns = UnitPattern.all()
for pattern in patterns:
  if not pattern.unit_pattern_set:
    pattern.unit_pattern_set = None
    pattern.put()

Edit: Also, the Updating you model's schema article discuss strategies you can use to handle schema changes such as this in future. However, that article is quite old and its method requires a web browser to keep hitting a url to trigger the next job to update more records - now that Task Queues exist, you could use a series of Tasks to make the change. The article on using deferred.defer has a framework you could utilise - it does a small amount of work, catches the DeadlineExceededError, and uses the handler to queue a new task which picks up where the current task left off.