This page covers the restrictions on querying Datastore from Google App Engine. The following list are common restrictions that you'll run into when developing for Datastore.
Entities lacking a property named in the query are ignored
Entities of the same kind need not have the same properties. To be eligible as a query result, an entity must possess a value (possibly null) for every property named in the query's filters and sort orders. If not, the entity is omitted from the indexes used to execute the query and consequently will not be included in the query's results.
Filtering on unindexed properties returns no results
A query can't find property values that aren't indexed, nor can it sort on such properties. See the Datastore Indexes page for a detailed discussion of unindexed properties.
Inequality filters are limited to at most one property
To avoid having to scan the entire index, the query mechanism relies on all of a query's potential results being adjacent to one another in the index. To satisfy this constraint, a single query may not use inequality comparisons (<, <=, >, >=, !=) on more than one property across all of its filters. For example, the following query is valid, because both inequality filters apply to the same property:
SELECT * FROM Person WHERE birth_year >= :min_birth_year
AND birth_year <= :max_birth_year
However, this query is not valid, because it uses inequality filters on two different properties:
SELECT * FROM Person WHERE birth_year >= :max_birth_year
AND height <= :max_height # ERROR
Note that a query can combine equality (=) filters for different properties, along with one or more inequality filters on a single property. Thus the following is a valid query:
Ordering of query results is undefined when no sort order is specified
When a query does not specify a sort order, the results are returned in the order they are retrieved. As the Datastore implementation evolves (or if an application's indexes change), this order may change. Therefore, if your application requires its query results in a particular order, be sure to specify that sort order explicitly in the query.
Sort orders are ignored on properties with equality filters
Queries that include an equality filter for a given property ignore any sort order specified for that property. This is a simple optimization to save needless processing for single-valued properties, since all results have the same value for the property and so no further sorting is needed. Multiple-valued properties, however, may have additional values besides the one matched by the equality filter. Because this use case is rare and applying the sort order would be expensive and require extra indexes, the Datastore query planner simply ignores the sort order even in the multiple-valued case. This may cause query results to be returned in a different order than the sort order appears to imply.
Properties used in inequality filters must be sorted first
To retrieve all results that match an inequality filter, a query scans the index for the first row matching the filter, then scans forward until it encounters a nonmatching row. For the consecutive rows to encompass the complete result set, they must be ordered by the property used in the inequality filter before any other properties. Thus if a query specifies one or more inequality filters along with one or more sort orders, the first sort order must refer to the same property named in the inequality filters. The following is a valid query:
SELECT * FROM Person WHERE birth_year >= :min_birth_year ORDER BY birth_year, last_nameThis query is not valid, because it doesn't sort on the property used in the inequality filter:
SELECT * FROM Person WHERE birth_year >= :min_birth_year ORDER BY last_name # ERRORSimilarly, this query is not valid because the property used in the inequality filter is not the first one sorted:
SELECT * FROM Person WHERE birth_year >= :min_birth_year
ORDER BY last_name, birth_year # ERROR
Properties with multiple values can behave in surprising ways
Because of the way they're indexed, entities with multiple values for the same property can sometimes interact with query filters and sort orders in unexpected and surprising ways.
If a query has multiple inequality filters on a given property, an entity will match the query only if at least one of its individual values for the property satisfies all of the filters. For example, if an entity of kind Widget has values 1 and 2 for property x, it will not match the query:
SELECT * FROM Widget WHERE x > 1
AND x < 2
Each of the entity's x values satisfies one of the filters, but neither single value satisfies both. Note that this does not apply to equality filters. For example, the same entity will satisfy the query
SELECT * FROM Widget WHERE x = 1
AND x = 2
even though neither of the entity's individual x values satisfies both filter conditions.
The not-equal (!=) operator works as a "value is other than" test. So, for example, the query
SELECT * FROM Widget WHERE x != 1
matches any Widget entity with an x value other than 1.
Similarly, the sort order for multiple-valued properties is unusual. Because such properties appear once in the index for each unique value, the first value seen in the index determines an entity's sort order:
- If the query results are sorted in ascending order, the smallest value of the property is used for ordering.
- If the results are sorted in descending order, the greatest value is used for ordering.
- Other values do not affect the sort order, nor does the number of values.
This has the unusual consequence that an entity with property values 1 and 9 precedes one with values 4, 5, 6, and 7 in both ascending and descending order.
Queries inside transactions must include ancestor filters
Datastore transactions operate only on entities belonging to the same entity group (descended from a common ancestor). To preserve this restriction, all queries performed within a transaction must include an ancestor filter specifying an ancestor in the same entity group as the other operations in the transaction.