Getting the "Closest" Value out of the Datastore

Question

What is the recommended (read most efficient) way to obtain a single entity out of the datastore based on a value that's closest to one provided.

For example, I have the following DataModel:

class MyObject(db.Model):
    someValue = db.IntegerProperty()
    shortDescription = db.TextProperty()

During a GET a value is passed in, I would like to query the datastore for the object(s) that are the closest to the provided value.

Multiple results are fine if they are the same distance. I.e. if I have two objects with someValue at 2 and 4 and I pass in 3.

Any advice would be appreciated.

Answer 1

An ascending and descending index is automatically created for all properties, so you can do something like this:

results1 = MyObject.all().order('someValue').filter('someValue >=', 3).fetch(50)
results2 = MyObject.all().order('-someValue').filter('someValue <', 3).fetch(50)

This will give you two result sets with up to 100 objects in the proximity of your search value.

Answer 2

The GQL query GqlQuery("SELECT * FROM MyObject WHERE someValue < :1 ORDER BY someValue DESC", input).get() will return the one item closest to and less than the input value.

To get the item closest to and greater than the input, switch it up: SELECT * FROM MyObject WHERE someValue > :1 ORDER BY someValue ASC"

Unfortunately I don't think there's a way to make this one query, but I may be wrong.