Python – Get records before and after current selection in Django query

djangodjango-modelsdjango-querysetpythonsql

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!

Best Solution

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).