How to get the nth record of a datastore query
Suppose I have a Foo model in GAE and this query:
query = Foo.all (). order ('- key )
I want to get the nth record. What is the most efficient way to achieve this?
Would the solution break if the ordering property is not unique, for example:
query = Foo.all (). order ('- color )
change: n> 1000
edit 2: I want to create a friendly paging engine that shows the available pages (eg Page 1, Page 2, ... Page 185) and requires "? page = x" in the query string, not "? bookmark = XXX". When page = x, the query should fetch records starting at the first record of this page.
source to share
There is no efficient way to do this - in any DBMS. In each case, you should at least read the index records sequentially until you find the nth, and then find the corresponding data record. This is more or less what sampling (count, offset) in GAE does, with an additional limit of 1000 records.
A better approach to this is to save a "bookmark" consisting of the field value you order for the last object you received and the entity key. Then, when you want to pick up where you left off, you can add the field value to the bottom of the inequality query and skip the entries until you compare or see the last one you saw.
If you want to provide user friendly page offsets, you can use memcache to store the relationship between the start offset and the root of the bookmark (order_property, key). When you create a page, insert or update a bookmark for the next item after the last one. When you fetch a page, use the bookmark if it exists, or generate it in a sophisticated way by running queries with offsets - potentially multiple queries if the offset is large enough.
source to share
The documentation for the Query class can be found at: http://code.google.com/appengine/docs/python/datastore/queryclass.html#Query
The query class provides a fetch that takes a limit and an offset in your case of 1 and n
Sample run time grows linearly with offset + limit
therefore, the only way to optimize in your case is to make sure that the necessary entries to access are most often close to the beginning of the array.
You can use query.filter ('key =', n) query.get ()
which will return the first match with key n
source to share