RE: Fetching next and/or prior objects given an arbitrary ordering

.id WHERE id= From: django-users@googlegroups.com [mailto:django-users@googlegroups.com] On Behalf Of Bernd Wechner Sent: Thursday, March 15, 2018 4:48 PM To: Django users Subject: Re: Fetching next and/or prior objects given an arbitrary ordering Mathtew, Yes I most certainly have. I use them elsewhe

Re: Fetching next and/or prior objects given an arbitrary ordering

; > > > *From:* django...@googlegroups.com [mailto: > django...@googlegroups.com ] *On Behalf Of *Bernd Wechner > *Sent:* Thursday, March 15, 2018 1:57 AM > *To:* Django users > *Subject:* Re: Fetching next and/or prior objects given an arbitrary > ordering > > > >

RE: Fetching next and/or prior objects given an arbitrary ordering

: Fetching next and/or prior objects given an arbitrary ordering Well the silence was stunning, so I pottered along and have a working solution. Alas I lean on a raw() call which I'd rather not. If there is any way to do this in the ORM I'd love to know. Otherwise I'm probably off to submit

Re: Fetching next and/or prior objects given an arbitrary ordering

Well the silence was stunning, so I pottered along and have a working solution. Alas I lean on a raw() call which I'd rather not. If there is any way to do this in the ORM I'd love to know. Otherwise I'm probably off to submit it as a feature request. The Window functions will all suffer this

Re: Fetching next and/or prior objects given an arbitrary ordering

Hmmm, really stuck on this. Can find no way of selecting from a select in the ORM. The whole premise seems be to start from model.objects and add SQL clauses with filter, annotate and other methods. But a QuerySet is not a model and queryset.objects doesn't exist, and queryset.filter just adds

Re: Fetching next and/or prior objects given an arbitrary ordering

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 ) AS prior, LEAD(id 1) OVER (ORDER BY ) AS next FROM ) result WHERE id=; There's

Re: Fetching next and/or prior objects given an arbitrary ordering

Good news is that Django 2.0 is out and does support the Window functions: https://docs.djangoproject.com/en/2.0/ref/models/database-functions/#lag Though it will mean a) upgrading on my dev box, c) working out how to use it and if happy, c) upgrading on my production box and rolling it out.

Re: Fetching next and/or prior objects given an arbitrary ordering

I too have a hard time envisioning the SQL, so yes, I tried that. Haven't got past the one I cited in first post yet: https://dba.stackexchange.com/questions/53862/select-next-and-previous-rows but it contains

Re: Fetching next and/or prior objects given an arbitrary ordering

I'm having a hard time envisioning the SQL statement that could do what you are asking, without a sub select or something like that. If you can write the expression in sql you _might_ be able to get back to the ORM that would create that. Can you provide an sql statement that does what you

Re: Fetching next and/or prior objects given an arbitrary ordering

On 1/03/2018 5:44 PM, Bernd Wechner wrote: Mike, Yep, adding pk as a final tie breaker is trivial, but not the issue ;-). Alas adding a sequencing field is not an option because I am looking for a generic solution, akin to what the admin site on Django offers, I have a model browser that I

Re: Fetching next and/or prior objects given an arbitrary ordering

Mike, Yep, adding pk as a final tie breaker is trivial, but not the issue ;-). Alas adding a sequencing field is not an option because I am looking for a generic solution, akin to what the admin site on Django offers, I have a model browser that I want to browse any of my models with.  And I

Re: Fetching next and/or prior objects given an arbitrary ordering

On 1/03/2018 10:50 AM, Bernd Wechner wrote: Julio, Thanks for giving it some though. But I think you misread me a little. I am using the get() only to illustrate that the precondition is, I have a single object. The goal then is find a neighboring object (as defined by the ordering in the

Re: Fetching next and/or prior objects given an arbitrary ordering

I should add that one solution which I find functional but unattractive is to build a and ordered list of PKs: things = list(Thing.objects.all().values_list('pk', flat=True)) then find the PK of the current object in that list and look one ahead or behind to get the PK of the neighbor and then

Re: Fetching next and/or prior objects given an arbitrary ordering

Julio, Thanks for giving it some though. But I think you misread me a little. I am using the get() only to illustrate that the precondition is, I have a single object. The goal then is find a neighboring object (as defined by the ordering in the model). Yes indeed, a filter is the first and

Re: Fetching next and/or prior objects given an arbitrary ordering

Hi Bernd, Well, the thing with `get()` is that it will return only one object. What you're looking for is `filter()`. Say, you want all the things that have an ID after a certain value. So you get `list_of_things = Things.objects.filter(pk__gte=...)`. Now it'll return the list, with all elements