Get records before and after current selection in Django query

Question

It sounds like an odd one but it's a really simple idea. I'm trying to make a simple Flickr for a website I'm building. This specific problem comes when I want to show a single photo (from my Photo model) on the page but I also want to show the image before it in the stream and the image after it.

If I were only sorting these streams by date, or was only sorting by ID, that might be simpler... But I'm not. I want to allow the user to sort and filter by a whole variety of methods. The sorting is simple. I've done that and I have a result-set, containing 0-many Photos.

If I want a single Photo, I start off with that filtered/sorted/etc stream. From it I need to get the current Photo, the Photo before it and the Photo after it.

Here's what I'm looking at, at the moment.

prev = None
next = None
photo = None

for i in range(1, filtered_queryset.count()):
    if filtered_queryset[i].pk = desired_pk:
        if i>1: prev = filtered_queryset[i-1]
        if i<filtered_queryset.count(): next = filtered_queryset[i+1]
        photo = filtered_queryset[i]
        break

It just seems disgustingly messy. And inefficient. Oh my lord, so inefficient. Can anybody improve on it though?

Django queries are late-binding, so it would be nice to make use of that though I guess that might be impossible given my horrible restrictions.

Edit: it occurs to me that I can just chuck in some SQL to re-filter queryset. If there's a way of selecting something with its two (or one, or zero) closest neighbours with SQL, I'd love to know!

Answer 1

I see the following possibilities:

1. Your URL query parameters contain the sort/filtering information and some kind of 'item number', which is the item number within your filtered queryset. This is the simple case - previous and next are item number minus one and plus one respectively (plus some bounds checking)

2. You want the URL to be a permalink, and contain the photo primary key (or some unique ID). In this case, you are presumably storing the sorting/filtering in:

   • in the URL as query parameters. In this case you don't have true permalinks, and so you may as well stick the item number in the URL as well, getting you back to option 1.
   • hidden fields in the page, and using POSTs for links instead of normal links. In this case, stick the item number in the hidden fields as well.
   • session data/cookies. This will break if the user has two tabs open with different sorts/filtering applied, but that might be a limitation you don't mind - after all, you have envisaged that they will probably just be using one tab and clicking through the list. In this case, store the item number in the session as well. You might be able to do something clever to "namespace" the item number for the case where they have multiple tabs open.

In short, store the item number wherever you are storing the filtering/sorting information.

Answer 2

You could try the following:

1. Evaluate the filtered/sorted queryset and get the list of photo ids, which you hold in the session. These ids all match the filter/sort criteria.
2. Keep the current index into this list in the session too, and update it when the user moves to the previous/next photo. Use this index to get the prev/current/next ids to use in showing the photos.
3. When the filtering/sorting criteria change, re-evaluate the list and set the current index to a suitable value (e.g. 0 for the first photo in the new list).