ndb datastore query cursor and indexing issues

user_3647998 0
Viewed 128

We are using ndb Datastore, python, standard google app engine. We would like to use Query Cursor. But for this to work according to here and here, it looks like we need to implement datastore_model.query().order(-datastore_model.key).

For example in our query we have 

teacher_model_query     = teacher_model.query(ndb.AND(
                ndb.GenericProperty('signinout_time') >= signinout_time_start, 
                ndb.GenericProperty('signinout_time') <= signinout_time_end))

teacher_query_forward = teacher_query.order(ndb.GenericProperty('signinout_time')).order(teacher__model.key)
teacher_query_reverse = teacher_query.order(-ndb.GenericProperty('signinout_time')).order(- 
 teacher__model.key)

Unfortunately this means that we have to create a new index for this

- kind: teacher_model
  properties:
  - name: signinout_time
    direction: desc
  - name: __key__
    direction: desc

This eats into the 200 indexes limit per project. Can you please confirm that we need to have order(-datastore.model.key) for query cursor to work in reverse direction? How can we execute Query cursor without the need to create additional indexes?

google-cloud-datastore google-app-engine python
2 Answers

You need to create such index only if your app needs the ability to move backwards in the query result pages. Which may be desired, for example, if you offer a page navigation functionality and you allow such navigation to a previous page.

But such functionality is usually not needed if you use cursors simply to split processing loads into smaller batches - in such cases you only navigate in one direction.

user_44950810

Changing my previous post as it was completely wrong.

According to https://cloud.google.com/datastore/docs/concepts/queries#limitations_of_cursors

An exception is if the original query's final sort order was on __key__. In that case, you can use the cursor in a reverse query, which is the original query with each sort order reversed. The reverse query can modify the start cursor, end cursor, offset, and limit.

So it seems like the last sort field has to be a key AND another index with all sorts reversed is required. This will consume two composite indexes.

One way to avoid this I can think of is to store all the cursors as a stack as the pagination is happening. This can be done on the client-side. Then as the user is navigating back or jumping to a specific previous page, the associated cursor can be used to always go forward. Say you have a page size of 25. Then you would have a cursor generated at 25, 50, 75 and 100. Then if the user wants to go back to 50, you would pick the associated cursor and generate rows from 50 to 75 which is iterating forward only.

user_46248770
Related