Implementing "Starts with" and "Ends with" queries with Google App Engine

Question

Am wondering if anyone can provide some guidance on how I might implement a starts with or ends with query against a Datastore model using Python?

In pseudo code, it would work something like...

Query for all entities A where property P starts with X

or

Query for all entities B where property P ends with X

Thanks, Matt

score 16 · Accepted Answer · answered Oct 12 '09 at 14:13

16

You can do a 'starts with' query by using inequality filters:

MyModel.all().filter('prop >=', prefix).filter('prop <', prefix + u'\ufffd')

Doing an 'ends with' query would require storing the reverse of the string, then applying the same tactic as above.

answered Oct 12 '09 at 14:13

Nick Johnson

Thanks. This worked in combination with storing the data in same case (either lower or upper). Appreciate the help :) – Matty Oct 12 '09 at 15:49
@Matty, yet another case in which nonrelational DBs require denormalization (based on knowing the important queries) for efficiency. Sigh, but, we'd better all get used to it!-) – Alex Martelli Oct 13 '09 at 02:23
Shouldn't this be ('prop <=', prefix + '\uffff') ? – Isaac Feb 10 '14 at 18:40
@Isaac \ufffd is the highest numbered valid character in the unicode basic multilingual plane - \ufffe-\uffff are not valid characters. – Nick Johnson Feb 11 '14 at 10:21
1

@Isaac In the unlikely even you expect Unicode replacement characters in your string, <= wouldn't be sufficient either, since 'foo\ufffd\ufffd' is still greater than 'foo\ufffd'. – Nick Johnson Feb 12 '14 at 10:36
This syntax is also valid: `MyModel.query(MyModel.prop >= prefix, MyModel.prop < prefix + u'\ufffd')` – Jeremy Mar 11 '15 at 01:19
1

@Jeremy That's valid with ndb, but not with db, which this answer was originally written for. – Nick Johnson Mar 11 '15 at 16:56

score 2 · Answer 2 · edited May 23 '17 at 10:29

2

Seems you can't do it for the general case, but can do it for prefix searches (starts with):

edited May 23 '17 at 10:29

Community

answered Oct 12 '09 at 13:34

Lee B

2 Answers2