OK Trying to implement this now and has SQL that works but can't work how to use the Django ORM to produce it. Here is the proforma SQL:
SELECT * FROM ( SELECT id, LAG(id, 1) OVER (ORDER BY <an order_by expression>) AS prior, LEAD(id 1) OVER (ORDER BY <an order_by expression>) AS next FROM <mytable> ) result WHERE id=<myid>; There's a sub query involved (as LAG and LEAD don't work if you constrain the inner query alas. And I've used SubQuery in Django before but not like this, (in the FROM clause), and am a tad stuck. Can anyone code this sort of query up in the Django ORM with QuerySets. I can create the inner set. result = model.objects.annotate(prior=Window(expression=Lag("pk"), order_by= order_by)).annotate(next=Window(expression=Lead("pk"), order_by=order_by)) Now the question is how to filter() the result of that, rather than that itself ;-). If that makes sense. Namely the aforementioned SQL. Any filter() I add to the end of this ORM QyerySet produces SQL more like SELECT id, LAG(id, 1) OVER (ORDER BY <an order_by expression>) AS prior, LEAD(id 1) OVER (ORDER BY <an order_by expression>) AS next FROM <mytable> WHERE id=<myid>; In with prior and next are empty, because that's just how such SQL works it seems. do the WHERE on the table produced by the SELECT/FROM as per SQL above to make this work. Regards, Bernd. -- You received this message because you are subscribed to the Google Groups "Django users" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscr...@googlegroups.com. To post to this group, send email to django-users@googlegroups.com. Visit this group at https://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/e4ba17aa-c21d-4b32-a50f-a613fae22a83%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.