In ndb, how to query for items where property A is

Question

In ndb, in order to query for items where property A is in list B, you can do something like:

Item.query(Item.A.IN(B))

How can you query for items where property A is not in list B?

Answer 1

It's not possible. Note that your IN query is actually automatically broken down into a number of different EQUALS queries, for each item in list B, and the merged results are returned.

You could query for all, and then manually filter out and ignore results that are in list B.

A typical GAE solution would be to denormalize and precompute another value that would be easy to index and query, and save that as a property in Item.

Answer 2

Never say never!

By constructing a series of 'x!=y AND x!=z' ndb filters, we can emulate a 'NOT IN' query: eg (1):

checkList = ['cookieAck', 'newkey', 'tempCelsius']
query = UserSetting.query(Setting.name!=checkList[0])
for check in checkList[1:]:
  query = query.filter(ndb.AND(UserSetting.name!=check))

settings = query.fetch()

Practically this looks like the following manually-constructed query (2):

  query = UserSetting.query(ndb.AND(ndb.AND(UserSetting.name!='cookieAck', UserSetting.name != 'newkey'), UserSetting.name != 'tempCelsius'))

Note that simply doing the following also works (3):

query = UserSetting.query(UserSetting.name!='cookieAck', UserSetting.name != 'newkey', UserSetting.name != 'tempCelsius')

Resulting Query:

Query(kind='UserSetting', filters=OR(AND(FilterNode('name', '<', 'cookieAck'), FilterNode('name', '<', 'newkey'), FilterNode('name', '<', 'tempCelsius')), AND(FilterNode('name', '<', 'cookieAck'), FilterNode('name', '<', 'newkey'), FilterNode('name', '>', 'tempCelsius')), AND(FilterNode('name', '<', 'cookieAck'), FilterNode('name', '>', 'newkey'), FilterNode('name', '<', 'tempCelsius')), AND(FilterNode('name', '<', 'cookieAck'), FilterNode('name', '>', 'newkey'), FilterNode('name', '>', 'tempCelsius')), AND(FilterNode('name', '>', 'cookieAck'), FilterNode('name', '<', 'newkey'), FilterNode('name', '<', 'tempCelsius')), AND(FilterNode('name', '>', 'cookieAck'), FilterNode('name', '<', 'newkey'), FilterNode('name', '>', 'tempCelsius')), AND(FilterNode('name', '>', 'cookieAck'), FilterNode('name', '>', 'newkey'), FilterNode('name', '<', 'tempCelsius')), AND(FilterNode('name', '>', 'cookieAck'), FilterNode('name', '>', 'newkey'), FilterNode('name', '>', 'tempCelsius'))))