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 was in fact using a date_time sequencer, only found that it broke in the generic sense for any ordering and with ties.

Moreover, without understanding the full context in your example you seem be fetching a pile of questions and then walking them to find the prior (I prefer the term "prior" as "last" has unfortunate ambiguity) and next questions, your set defined by "instruction_id=question.instruction.pk". My guess is that in your scenario that list is likely to be small (<1000 objects ;-) and this strategy becomes unappealing again if that list could be arbitrarily large (millions, or more) which is true for a generic scenario which makes no assumptions about the model other than what is provided by its ordering and known about the current object.

Regards,

Bernd.

Mike Dewhirst wrote:

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 model).  >  > Yes indeed, a filter is the first and primary candidate for achieving   > that, but can you write one that respects an abritrary ordering   > involving multiple fields, as I exemplified with:  >  > |  > classThing(models.Model):  >      field1 =...  >      field2 =...  >      field2 =...  > classMeta:  >          ordering =['field1','-field2','field3']  > |  >  > Also consider that the ordering thus specified does not stipulate   > uniqueness in any way, that is many neighboring things in an ordered   > list may have identical values of field1, field2 and field3.    Nothing to stop you adding 'pk' or '-pk' to the ordering list.    I have done something similar recently where an online training course   leads the user with next and previous links to the next or previous   questions. At the first and last question for each set of instruction,   the links morph into previous and next instruction.    My approach relies on there being only a few questions per instruction.   It isn't very elegant because it tramples over the 'last' values until   the last man standing is correct and it bails out as soon as it finds a   greater 'next' value.    I did need/want a separate numeric ordering field because a trainer   writing up a course needs a sequence to force instruction items and   questions into adjustable arbitrary orders. Hence, the model save()   method updates a num_sequence float field from the user entered sequence   char field.    I haven't thought about < or > in the context of your ['field1',   '-field2', 'field3'] ordering but I went for a num_sequence float field   because '11' < '2' but 2.0 < 11.0 and so on.    The view code goes like this ...    def get_last_next_question_links(question, instruction, course, user):       lastlink = 'No previous question'       nextlink = 'No more questions'       questions = Question.objects.filter(instruction_id=question.instruction.pk)       for obj in questions:           if obj.num_sequence < question.num_sequence:               lasturl = '/question/%s/' % obj.pk               lastname = 'Previous Question %s' % obj.sequence               lastlink = '<a href="%s">%s</a>' % (lasturl, lastname)           elif obj.num_sequence > question.num_sequence:               nexturl = '/question/%s/' % obj.pk               nextname = 'Next Question %s' % obj.sequence               nextlink = '<a href="%s">%s</a>' % (nexturl, nextname)               break       if lastlink.startswith('No'):           lastlink = get_instruction_link(               course, instruction, user, next=False           )       if nextlink.startswith('No'):           nextlink = get_instruction_link(               course, instruction, user, next=True           )       return lastlink, nextlink      hth    >  > I'm not sure how Django sorts those ties, but imagine it either defers   > to the underlying database engine (i.e. uses the sort simply to   > generate an ORDER BY clause in the SQL for example in the above case:  >  > |  > ORDER BY field1 ASC,field2 DESC,field3 ASC  > |  >  > and lets the underlying database engine define how ties are ordered.   > Or it could add a pk tie breaker to the end. Matters little, the   > problem remains: how to find neighbors given an arbitrary ordering and   > ties.  >  > Can you write a filter clause to do that? I'm curious on that front.  >  > It's easy of course with one sort field with unique values collapsing   > to an __gt or __lt filter folllowed by first() or last() respectively   > (not sure that injects a LIMIT clause into the SQL or collects a list   > and then creams one element from it - I'll test a little I think).  >  > In the mean time, I still feel this has to be a fairly standard use   > case. It's about browsing objects in a table one by one, with a next   > and previous button given an ordering specified in the model and no   > guarantee of uniqueness on the (sort keys).  >  > Regards,  >  > Bernd.  >  > On Thursday, 1 March 2018 00:58:58 UTC+11, Julio Biason wrote:  >  >     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 after the one you asked.  >  >     If you want the previous and next, you can do `list_of_previous =  >     Things.objects.filter(pk__lt=...).limit(1)` and `list_of_next =  >     Things.objects.filter(pk__gt).limit(1)`.  >  >     Or something like that ;P  >  >     On Wed, Feb 28, 2018 at 8:56 AM, Bernd Wechner  >     <bernd....@gmail.com <javascript:>> wrote:  >  >         I'm a bit stumped on this. Given an arbitrary ordering as  >         specified by the ordering meta option:  >  >         https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering  >         <https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering>  >  >         for example:  >  >             class Thing(models.Model):  >                 field1 = ...  >                 field2 = ...  >                 field2 = ...  >                 class Meta:  >                     ordering = ['field1', '-field2', 'field3']  >  >         given an instant of Thing:  >  >             thing = Thing.objects.get(pk=...)  >  >         how can I get the next Thing after that one, and/or the prior  >         Thing before that one as they appear on the sorted list of Things.  >  >         It's got me stumped as I can't think of an easy way to build a  >         filter even with Q object for an arbitrary ordering given  >         there can be multiple fields in ordering and multiple Things  >         can have the same ordering list (i.e. there can be ties - that  >         Django must resolve either arbitrarily or with an implicit pk  >         tie breaker on ordering).  >  >         It's got me stumped. I can solve any number of simpler  >         problems just not his generic one (yet).  >  >         Ideally I'd not build a list of all objects (waste of memory  >         with large collections), and look for my thing in the list and  >         then pick out the next or prior.  >  >         I'd ideally like to fetch it in one query returning the one  >         Thing, or if not possible no worse than returning all Things  >         on side of it and picking off the first or last respectively  >         (even that's kludgy IMHO).  >  >         I'm using postgresql and I found a related question here:  >  >         https://dba.stackexchange.com/questions/53862/select-next-and-previous-rows  >         <https://dba.stackexchange.com/questions/53862/select-next-and-previous-rows>  >  >         but would rather stick with the ORM and not even explore SQL  >         (just took a peak to see SQL can be constructed to do it I  >         guess, as if not, the ORM sure won't have a good way of doing  >         it methinks).  >  >         I'd have thought this a sufficiently common use case but am  >         perhaps wrong there, with most sites exploiting simple  >         orderings (like date_time or creation say). But I want to  >         build a generic solution that works on any model I write, so I  >         can walk through the objects in the order specified by  >         ordering, without building a list of all of them. In short I  >         want to solve this problem, not reframe the problem or work  >         around it ;-).  >  >         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...@googlegroups.com  >         <javascript:>.  >         To post to this group, send email to  >         django...@googlegroups.com <javascript:>.  >         Visit this group at  >         https://groups.google.com/group/django-users  >         <https://groups.google.com/group/django-users>.  >         To view this discussion on the web visit  >         https://groups.google.com/d/msgid/django-users/751c367c-d5e9-e06b-8f5c-82054f11a9ab%40gmail.com  >         <https://groups.google.com/d/msgid/django-users/751c367c-d5e9-e06b-8f5c-82054f11a9ab%40gmail.com?utm_medium=email&utm_source=footer>.  >         For more options, visit https://groups.google.com/d/optout  >         <https://groups.google.com/d/optout>.  >  >  >  >  >     --   >     *Julio Biason*,Sofware Engineer  >     *AZION*  | Deliver. Accelerate. Protect.  >     Office: +55 51 3083 8101 |  Mobile: +55 51 _99907 0554_  >  > --   > 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+unsubscribe@googlegroups.com   > <mailto:django-users+unsubscribe@googlegroups.com>.  > To post to this group, send email to django-users@googlegroups.com   > <mailto: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/bf6698d0-9aa5-4251-be69-62fe53afb603%40googlegroups.com   > <https://groups.google.com/d/msgid/django-users/bf6698d0-9aa5-4251-be69-62fe53afb603%40googlegroups.com?utm_medium=email&utm_source=footer>.  > For more options, visit https://groups.google.com/d/optout.