Backward pagination with cursor is working but missing an item

Question

From looking for ideas/alternatives to providing a page/item count/navigation of items matching a GAE datastore query, I could find a hint how to backward page navigation with a single cursor by REVERSING ORDER.

class CursorTests(test_utils.NDBTest):

  def testFirst(self):
    class Bar(model.Model):
      value = model.IntegerProperty()

    self.entities = []
    for i in range(10):
        e = Bar(value=i)
        e.put()
        self.entities.append(e)

    q = Bar.query()
    bars, next_cursor, more = q.order(Bar.key).fetch_page(3)
    barz, another_cursor, more2 = q.order(-Bar.key).fetch_page(3, start_cursor=next_cursor)
    self.assertEqual(len(bars), len(barz))

Unfortunately it failed with this error.

Traceback (most recent call last): File "/Users/reiot/Documents/Works/appengine-ndb-experiment/ndb/query_test.py", line 32, in testFirst self.assertEqual(len(bars), len(baz)) AssertionError: 3 != 2

Yes, an item in boundary is missing with reverse query.

bars = [Bar(key=Key('Bar', 1), value=0), Bar(key=Key('Bar', 2), value=1), Bar(key=Key('Bar', 3), value=2)] 
bars = [Bar(key=Key('Bar', 2), value=1), Bar(key=Key('Bar', 1), value=0)]

How can I fix this problem?

Answer 1

Ok, here's the official answer. You need to "reverse" the cursor, as follows:

rev_cursor = cursor.reversed()

I did not know this myself. :-( I'll make sure this is shown in the docs for fetch_page().

Answer 2

Dealing with these multiple cursors, plus forward and reverse queries not only is too complicated, but does not allow direct paging (going to page 7), with a set of page links at the bottom of page like so "<< 1 2 3 4 5 >>", since you have no idea how many pages there will be.

For this reason, my solution would be to fetch the whole result set, or at least a significant result set, for example corresponding to 10 pages, then doing simple divisions to handle pages. In order to not waste Ndb bandwidth (and costs), you would first fetch the results with keys_only=True. After you have determined the set that corresponds to your current page, you do the key.get() on your entities. And if you want you can consider saving the full list of keys in memcache for a few minutes so the query is not rerun, though I haven't found this to be necessary so far.

This is an example implementation:

def session_list():
    page = request.args.get('page', 0, type=int)

    sessions_keys = Session.query().order(-Session.time_opened).fetch(100, keys_only=True)
    sessions_keys, paging = generic_list_paging(sessions_keys, page)
    sessions = ndb.get_multi(sessions_keys)

    return render_template('generic_list.html', objects=sessions, paging=paging)

It's making use of a generic_list_pagingfunction that does the paging divisions and extracting the proper sublist within the result set:

def generic_list_paging(objects, page, page_size=10):
    nb_items = len(objects)
    item_start = min(page * page_size, nb_items)
    item_end = min((page + 1) * page_size, nb_items)
    page_max = (nb_items - 1) // page_size + 1
    objects = objects[item_start: item_end]
    paging = {'page': page, 'page_max': page_max}
    return objects, paging

Finally, if you are using Jinja2, here's the paging navigation using the pagingdict:

{% if paging.page_max > 1 %}
        <nav>
            <ul class="pagination">
                {% if paging.page > 0 %}
                    <li>
                        <a href="{{ request.path }}?page={{ paging.page-1 }} aria-label="Previous">
                            <span aria-hidden="true">&laquo;</span>
                        </a>
                    </li>
                {% endif %}
                {% for page in range(0,paging.page_max) %}
                    <li {% if page==paging.page %}class="disabled"{% endif %}><a href="{{ request.path }}?page={{ page }}">{{ page+1 }}</a></li>
                {% endfor %}
                {% if paging.page < paging.page_max-1 %}
                    <li>
                        <a href="{{ request.path }}?page={{ paging.page+1 }}" aria-label="Next">
                            <span aria-hidden="true">&raquo;</span>
                        </a>
                    </li>
                {% endif %}
            </ul>
        </nav>
{% endif %}