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.
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()
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
Actually it does. There’s no requirement that a Subquery makes any reference to an
So I tried this with an arbitrary table - I believe you’ll see the parallel is close enough…
idx = models.AutoField(primary_key=True)
title = models.CharField(max_length=100)
name = models.CharField(max_length=100)
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.
Of course now the solution is quite obvious Forgot I can easily filter by