What is the correct way to get the previous page of results given an NDB cursor?

Question

I'm working on providing an API via GAE that will allow users to page forwards and backwards through a set of entities. I've reviewed the section about cursors on the NDB Queries documentation page, which includes some sample code that describes how to page backwards through query results, but it doesn't seem to be working as desired. I'm using GAE Development SDK 1.8.8.

Here's a modified version of that example that creates 5 sample entities, gets and prints the first page, steps forward into and prints the second page, and attempts to step backwards and print the first page again:

import pprint
from google.appengine.ext import ndb

class Bar(ndb.Model):
    foo = ndb.StringProperty()

#ndb.put_multi([Bar(foo="a"), Bar(foo="b"), Bar(foo="c"), Bar(foo="d"), Bar(foo="e")])

# Set up.
q = Bar.query()
q_forward = q.order(Bar.foo)
q_reverse = q.order(-Bar.foo)

# Fetch the first page.
bars1, cursor1, more1 = q_forward.fetch_page(2)
pprint.pprint(bars1)

# Fetch the next (2nd) page.
bars2, cursor2, more2 = q_forward.fetch_page(2, start_cursor=cursor1)
pprint.pprint(bars2)

# Fetch the previous page.
rev_cursor2 = cursor2.reversed()
bars3, cursor3, more3 = q_reverse.fetch_page(2, start_cursor=rev_cursor2)
pprint.pprint(bars3)

(FYI, you can run the above in the Interactive Console of your local app engine.)

The above code prints the following results; note that the third page of results is just the second page reversed, instead of going back to the first page:

[Bar(key=Key('Bar', 4996180836614144), foo=u'a'),
 Bar(key=Key('Bar', 6122080743456768), foo=u'b')]
[Bar(key=Key('Bar', 5559130790035456), foo=u'c'),
 Bar(key=Key('Bar', 6685030696878080), foo=u'd')]
[Bar(key=Key('Bar', 6685030696878080), foo=u'd'),
 Bar(key=Key('Bar', 5559130790035456), foo=u'c')]

I was expecting to see results like this:

[Bar(key=Key('Bar', 4996180836614144), foo=u'a'),
 Bar(key=Key('Bar', 6122080743456768), foo=u'b')]
[Bar(key=Key('Bar', 5559130790035456), foo=u'c'),
 Bar(key=Key('Bar', 6685030696878080), foo=u'd')]
[Bar(key=Key('Bar', 6685030696878080), foo=u'a'),
 Bar(key=Key('Bar', 5559130790035456), foo=u'b')]

If I change the "Fetch the previous page" section of code to the following code snippet, I get the expected output, but are there any drawbacks that I haven't forseen to using the forward-ordered query and end_cursor instead of the mechanism described in the documentation?

# Fetch the previous page.
bars3, cursor3, more3 = q_forward.fetch_page(2, end_cursor=cursor1)
pprint.pprint(bars3)

Vamoose

#14543508 --> same question by me – Kalliekallista

Thanks @zho. I read every question related to reverse paging with ndb cursors that I could find, including yours, before I posted the question. They are similar questions, but I don't think my example had the same problem that your question had (reversing an already reversed cursor). I also tried to boil down my example to something that can be run in the interactive console and takes web frameworks out of the equation. – Vamoose

Looks like solution with end_cursor will always display only first page. Try to get second page from third. (does not work for me). – Coachman

Answer 1

To make the example from the docs a little clearer let's forget about the datastore for a moment and work with a list instead:

# some_list = [4, 6, 1, 12, 15, 0, 3, 7, 10, 11, 8, 2, 9, 14, 5, 13]

# Set up.
q = Bar.query()

q_forward = q.order(Bar.key)
# This puts the elements of our list into the following order:
# ordered_list = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15]

q_reverse = q.order(-Bar.key)
# Now we reversed the order for backwards paging: 
# reversed_list = [15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 5, 4, 3, 2, 1, 0]

# Fetch a page going forward.

bars, cursor, more = q_forward.fetch_page(10)
# This fetches the first 10 elements from ordered_list(!) 
# and yields the following:
# bars = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9]
# cursor = [... 9, CURSOR-> 10 ...]
# more = True
# Please notice the right-facing cursor.

# Fetch the same page going backward.

rev_cursor = cursor.reversed()
# Now the cursor is facing to the left:
# rev_cursor = [... 9, <-CURSOR 10 ...]

bars1, cursor1, more1 = q_reverse.fetch_page(10, start_cursor=rev_cursor)
# This uses reversed_list(!), starts at rev_cursor and fetches 
# the first ten elements to it's left:
# bars1 = [9, 8, 7, 6, 5, 4, 3, 2, 1, 0]

So the example from the docs fetches the same page from two different directions in two differents orders. This is not what you want to achieve.

It seems you already found a solution that covers your use case pretty well but let me suggest another:

Simply reuse cursor1 to go back to page2.
If we're talking frontend and the current page is page3, this would mean assigning cursor3 to the 'next'-button and cursor1 to the 'previous'-button.

That way you have to reverse neither the query nor the cursor(s).

Alix

This is helpful, but I was hoping for a solution that worked with a single cursor so that I don't need to keep track of any additional state across web requests. I think if the user goes forward multiple times, I'd need to keep a stack of "back" cursors in order to be able to allow them to go back multiple times - am I understanding this correctly? – Vamoose

When you do the reverse query, this will return a new cursor (in this case cursor1). This cursor points at the point past 0 in bars1. You could then use this cursor to go back another page. – Cedric

Answer 2

I took the liberty of changing the Bar model to a Character model. The example looks more Pythonic IMO ;-)

I wrote a quick unit test to demonstrate the pagination, ready for copy-pasting:

import unittest

from google.appengine.datastore import datastore_stub_util
from google.appengine.ext import ndb
from google.appengine.ext import testbed


class Character(ndb.Model):
    name = ndb.StringProperty()

class PaginationTest(unittest.TestCase):
    def setUp(self):
        tb = testbed.Testbed()
        tb.activate()
        self.addCleanup(tb.deactivate)
        tb.init_memcache_stub()
        policy = datastore_stub_util.PseudoRandomHRConsistencyPolicy(
            probability=1)
        tb.init_datastore_v3_stub(consistency_policy=policy)

        characters = [
            Character(id=1, name='Luigi Vercotti'),
            Character(id=2, name='Arthur Nudge'),
            Character(id=3, name='Harry Bagot'),
            Character(id=4, name='Eric Praline'),
            Character(id=5, name='Ron Obvious'),
            Character(id=6, name='Arthur Wensleydale')]
        ndb.put_multi(characters)
        query = Character.query().order(Character.key)
        # Fetch second page
        self.page = query.fetch_page(2, offset=2)

    def test_current_page(self):
        characters, _cursor, more = self.page
        self.assertSequenceEqual(
            ['Harry Bagot', 'Eric Praline'],
            [character.name for character in characters])
        self.assertTrue(more)

    def test_next_page(self):
        _characters, cursor, _more = self.page
        query = Character.query().order(Character.key)
        characters, cursor, more = query.fetch_page(2, start_cursor=cursor)

        self.assertSequenceEqual(
            ['Ron Obvious', 'Arthur Wensleydale'],
            [character.name for character in characters])
        self.assertFalse(more)

    def test_previous_page(self):
        _characters, cursor, _more = self.page
        # Reverse the cursor (point it backwards).
        cursor = cursor.reversed()
        # Also reverse the query order.
        query = Character.query().order(-Character.key)
        # Fetch with an offset equal to the previous page size.
        characters, cursor, more = query.fetch_page(
            2, start_cursor=cursor, offset=2)
        # Reverse the results (undo the query reverse ordering).
        characters.reverse()

        self.assertSequenceEqual(
            ['Luigi Vercotti', 'Arthur Nudge'],
            [character.name for character in characters])
        self.assertFalse(more)

Some explanation:

The setUp method first initializes the required stubs. Then the 6 example characters are put with an id so the order isn't random. Since there are 6 characters we have 3 pages of 2 characters. The second page is fetched directly using an ordered query and an offset of 2. Note the offset, this is key for the example.

test_current_page verifies that the two middle characters are fetched. Characters are compared by name for readability. ;-)

test_next_page fetches the next (third) page and verifies the names of the expected characters. Everything is quite straight forward so far.

Now test_previous_page is interesting. This does a couple of things, first the cursor is reversed so the cursor now points backwards instead of forward. (This improves readability, it should work without this, but the offset will be different, I'll leave this as an exercise for the reader.) Next a query is created with a reverse ordering, this is necessary because the offset cannot be negative and you want to have previous entities. Then results are fetched with an offset equal to the page length of the current page. Else the query will return the same results, but reversed (like in the question). Now because the query was reverse-ordered the results are all backwards. We simply reverse the results list in-place to fix this. Last but not least, the expected names are asserted.

Side note: Since this involves global queries the probability is set to 100%, in production (because of the eventual consistency) putting and querying right after will most likely fail.

Chadbourne

Thanks. I must say that the implementation of cursors in ndb is fairly incompetent. The fact that you have to use offset and reverse the results... some people are special in making simple things end up messy. Thanks for the help. – Thrush