GAE Datastore Queries

• Like a query in traditional DB

• Ask for Entities of a certain Kind with zero or more conditions and optional sorting requirements

A query includes:

• The kind of the entities to query

• Zero or more filters, criteria that property values must meet for an entity to be returned by the query

• Zero or more sort orders that determine the order in which results are returned
based on property values

NOTE: A query based on property values can only return entities of a single kind.
                    This is the primary purpose of kinds: to determine which entities are considered together as possible results for a query.

Query Results --full Entities returned

All Entities returned are full (you do not return only some of the parameters--unlike in relational DB)

PERFORMANCE TIP--- Store multiple copies of data (different fields) in different kinds when want to often access only part of a large Entity ---> Split it into 2 or more different kinds of Entities. If you have objects with large property values or very many properties and you only need to access a subset of this data most of the time, you can speed up access by storing the data across multiple entities.   One entity stores the often accessed properties and properties used in queries, as well as a reference to another entity carrying the larger data (the key of the other entity)

 

 

GQL --- Google Query Language (admin console and Python only--NO java)

Like SQL but for GAE datastore (reduced set of instructions)

Example:

SELECT * FROM Player WHERE level >= 5 AND level <= 20 ORDER BY level ASC, score DESC

 

ONLY in Python NOT JAVA

Use GAE Admin Console to investigate current Datastore

• Use GQL to browse the contents of the datastore of your live application using
  the Administration Console.
• GAE Admin Consolue -> Data Viewer
• browse entities by kind, or enter a GQL query.
• create new entities through the Console, though new entities are limited to the kinds, properties, and value types
  of existing entities.
• Edit the properties of an entity by clicking on its ID.
• You can only perform a query in the Administration Console if the query does not require a custom index, or if the app already has the requisite index for the query.
• 
  

 

Low-Level Java API to perform Query

Query query = new Query("Greeting", guestbookKey).addSort("date", Query.SortDirection.DESCENDING);
List<Entity> greetings = datastore.prepare(query).asList(FetchOptions.Builder.withLimit(5));

• creates a new query on the Greeting entity
• sets the guestbookKey as the required parent entity for all entities that will be returned.
• sort on the date property
• return the newest Greeting first.
• query results returned as a list of Entity objects

see the Datastore reference.

Method	meaning
addFilter()	setup condition like "where" clause for query example of kind "Person": q.addFilter("lastName", Query.FilterOperator.EQUAL, lastNameParam); q.addFilter("height", Query.FilterOperator.LESS_THAN, maxHeightParam);
addSort()	setup condition like "order by" clause for query example of kind "Person": q.addSort("firstName");

NOTE: The query is not actually performed until you attempt to access results using the
PreparedQuery object.

Example cyling through results // Get the Datastore Service DatastoreService datastore = DatastoreServiceFactory.getDatastoreService(); // The Query interface assembles a query Query q = new Query("Person"); q.addFilter("lastName", Query.FilterOperator.EQUAL, lastNameParam); q.addFilter("height", Query.FilterOperator.LESS_THAN, maxHeightParam); // PreparedQuery contains the methods for fetching query results // from the datastore PreparedQuery pq = datastore.prepare(q); //cycle through results for (Entity result : pq.asIterable()) {   String firstName = (String) result.getProperty("firstName");   String lastName = (String) result.getProperty("lastName");   Long height = (Long) result.getProperty("height");   System.out.println(firstName + " " + lastName + ", " + height + " inches tall"); }

 

 

Example 2

DatastoreService ds = DatastoreServiceFactory.getDatastoreService();

Query q = new Query("Book");       //get Entities of kind "Book"
q.addFilter("copyrightYear", Query.FilterOperator.LESS_THAN_OR_EQUAL, 1950);
q.addSort("title");


PreparedQuery pq = ds.prepare(q);

for (Entity result : pq.asIterable()) {
       String title = result.getProperty("title");
       // ...
}

 

 

Getting only 10 Entities skipping the first 20

ALTERNATIVE -- ask for only 10 results starting with the 21st results (skip first 20) -- still pay for all 30

// Query q = ...
PreparedQuery pq = ds.prepare(q);

Iterable<Entity> results = pq.asIterable(FetchOptions.Builder.withLimit(10).offset(20));

for (Entity result : results) {
String title = (String) result.getProperty("title");
// ...
}

Getting ALL entities

PreparedQuery pq = ds.prepare(q);
pq.asList()          or          ps.asList(FetchOptions***); 

Unlike the iterator interface, which gets
results in batches, this method retrieves all results with a single service call. The method
requires that a limit be specified using FetchOptions, up to the maximum of 1,000
entities.

Getting only 1 Entity --- throws exception if more than 1 Entity is returned (test for this)

PreparedQuery pq = ds.prepare(q);
pq.asSingleEntity() 

retrieves the result and returns an Entity object, or null.

obviously no FetchOptions given in asSingleEntity() as only 1 entity want.

NOTE: Throws PreparedQuery.TooManyResultsException - if more than one result is returned from the Query.

Getting only count of Entities that would be returned

PreparedQuery pq = ds.prepare(q);
pq.countEntities()            or          ps.countEntities(FetchOptions***); 
If you just want a count of the results and not the entities themselves

Because the datastore has to perform the query to get the count, the speed of this call is proportional to the count, though faster than actually fetching the results by a constant factor.
Like an asList() fetch, the count is also limited to a maximum of 1,000 entities.

 

 

Queries that return only the Keys of Entities rather than Entities

You can fetch just the keys for the entities that match a query instead of the full entities using the low-level Java datastore API.

 

   Query q = new Query("Book");
   q.setKeysOnly();
    

What is returned --> actually Entities with no properties ---> you grab the corresponding keys using getKey() of Entity

   PreparedQuery pq = ds.prepare(q);
   for (Entity result : pq.asIterable()) {
        Key k = result.getKey();
        // ...
   }

You can also perform keys-only queries using the JDO and JPA interfaces.