muse

Google App Engine: Pagination by date

PAGESIZE = 10 
 
class Story(db.Model): 
  content = db.StringProperty() 
  when = db.DateTimeProperty(auto_now_add=True) 

I. Efficient Paging (with a next button)

Source: Paging through large datasets

A. Example Url

/Newest?bookmark=2009-11-06T17:17:34 

B. Code

def get(self): 
  stories = Story.all().order('-when').fetch(PAGESIZE+1) 
  next = None 
  bookmark = self.request.get('bookmark')
#bookmark = next in template


  if bookmark: 
    query = Story.all().order('-when') 
    query.filter('when <=', bookmark) 
    stories = query.fetch(PAGESIZE+1) 
  else: 
    #first page or last page 
    query = Story.all().order('-when') 
    stories = query.fetch(PAGESIZE+1) 
  if len(stories) == PAGESIZE + 1: 
    next = stories[-1].when 
    stories = stories[:PAGESIZE] 
#'next' and 'stories' are template variables 

Template:

{% if next %} 
Next Page 
{% endif %} 

C. Does it work? Not yet!

This is what you'll end up with if you read the first part of Google's tutorial on paging. And it's supposed to just work -- but it doesn't.

The 'when' attribute is stored as a datetime.datetime object, which look like this:

datetime.datetime(2009, 11, 6, 12, 20, 5) 

Passing it to a template converts it into a string. By default, it'll be converted into ISO format:

'2009-11-06 12:20:05' 

But when you retrieve this string from your page, it won't compare well with datetime.datetime objects. Converting it is relatively easy though. Convert it before you send it to the template like this:

import datetime 
 
next = stories[-1].when.isoformat() 

And then convert it back after retrieving it:

if bookmark: 
  dt = datetime.datetime.strptime(bookmark, "%Y-%m-%dT%H:%M:%S") 
  dt.replace(microsecond=0) 

I needed to set microsecond to 0 on my local machine in order to get this to work. YMMV

Tips:

1. Instead of passing a date as the 'next page' link, pass the ID of the next story. Then on the next page fetch this objects date to figure out where to start. 

In order to avoid a datastore lookup, use memcache.

2. On every page store the first element's date in memcache so you can look it up on the next page to use for a 'previous page' link.

Source: Discussion

D. Downsides

If two stories are submitted at the same second and one of your pages is set to start filtering at that datetime value, you'll be missing a story. This is really bad.

For a solution to this problem, see the original paging article from Google. And source code .

II. Simple Paging (less efficient, perhaps more useful)

A. Example Url

/Newest?page=2 

B. Code

PAGESIZE = 10 
 
def get(self): 
  max_results = 1000 max_pages = (max_results - PAGESIZE) / PAGESIZE page = self.request.get_range('page', min_value=0, max_value=max_pages, default=0) 
  start = page*PAGESIZE 
  stories = Story.all().order('-when').fetch(PAGESIZE+1, start) 
  more_stories = len(stories) > PAGESIZE 
 
  prev_page = None 
  if page: #if page == 0 == False, we're on the first page 
    prev_page = str(page - 1) 
 
  next_page = None 
  if more_stories: 
    next_page = str(page + 1) 
 
  stories = stories[:PAGESIZE] 
 # 'stories' and 'next_page' and 'prev_page' 
# are template variables 

Template:

{% if next_page %} 
Next Page 
{% endif %} 
 
{% if prev_page %} 
Previous Page 
{% endif %} 

C. Explanation

On the first page, the value of 'page' will be its default value: 0

So 'start' will also equal 0. We fetch our PAGESIZE + 1 (10 + 1), and specify an offset of start (0).

If there are 20 stories in the database, fetch will return the first 11. If there are 8 stories in our database, fetch will return 8.

On the current page we'll need the first 10 stories, but we need to figure out if there are more than 10 stories so we can display a "Next Page" link.

If we fetched 11 stories, len(stories) will be greater than PAGESIZE. And if that's the case, then there's a next page! We'll set 'next_page' equal to the current page's value (0) plus 1.

The reason this is inefficient is because once we get to the 5th page we'll be fetching 11 stories while using an offset of 50. The way fetch works, it will get 61 stories, discard the first 50, and then give us the remainder. All we want is 11 stories, but we'll end up incurring the time and processing power required to get 61 of them.

D. Some Tips

1. Convert the values of next_page and prev_page into strings because in the template {% if prev_page %} would evaluate to False if prev_page was equal to 0.

2. If you know you're going to be returning a lot of results, link to the first few pages directly.

[1] 
[2] 
[3]