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.
