Get "next" and "previous" objects with Django ORM based on arbitrary sort?

Using Django Using the ORM
1

Hello,

I have a model for posts on my site and they have order property which lets me manually set the order they are displayed in a list.

On the detail page, I would like to show “Read next” which is next post in the sequence. Based on the slug I fetch the post to display. What is the best way to get the one that follows it in ordered list based on order?

I cannot simply take the order property and increment it by one, because the ordering numbers aren’t sequential. They may be something like this 1,2,3,10,15 etc.

The only approach that came to mind is to do two queries and then do this logic in memory. Loop over the list and once I find the current post, take the one immediately after it. Which doesn’t seem like an optimal solution.

I feel like I am missing something fairly obvious?

Here is “illustration of the data model”.

Post A (Order=1)
Post B (Order=2)
Post C (Order=3)
Post D (Order=10)

I get Post C with Order 3 via slug and need to fetch Post D in this case.

2

You may be able to do this with a subquery - in terms of a pseudo-code notation (I’m winging this), I’m thinking of something like:

next_post = Post.objects.filter(order__gt=Subquery(Post.objects.get(slug=current_row_slug).order)).order_by('order').first()

1 Like
3

Subquery doesn’t work like that - it only takes a queryset which should have an OuterRef in at least one filter.

But yes, the essence of the solution sounds like that I was thinking

current_post = Post.objects.get(slug="c")
next_post = Post.objects.filter(order__gt=current).order_by('order').first()

Like this you’ll need to handle .first() maybe returning None, when you’re at the end.

You’ll want to ensure order is indexed, perhaps alongside other columns depending on what other queries use order.

1 Like
4

Actually it does. There’s no requirement that a Subquery makes any reference to an OuterRef

So I tried this with an arbitrary table - I believe you’ll see the parallel is close enough…
The model:

class PKTest(models.Model):
    idx = models.AutoField(primary_key=True)
    title = models.CharField(max_length=100)
    name = models.CharField(max_length=100)

This query:
PKTest.objects.filter(idx__gt=Subquery(PKTest.objects.filter(title='This is a test').values('idx')).order_by('idx')

generates this SQL:

SELECT "fs_pktest"."idx", "fs_pktest"."title", "fs_pktest"."name" FROM "fs_pktest" 
WHERE "fs_pktest"."idx" > (SELECT U0."idx" FROM "fs_pktest" U0 WHERE U0."title" = This is a test)

And returns the desired result.

1 Like
5

Thanks both!

Of course now the solution is quite obvious :smiley: Forgot I can easily filter by gt