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 fetch it with get(). The problem with this is that it loads an arbitrarily large list of PKs into memory for a job that should have a solution in the form of a database query that the ORM can execute lazily and receiving just one object.

Note that the list of Things above is ordered by Django in respect of the ordering defined in the model meta class, that is, this is an ordered list.

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> 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

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

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.
To post to this group, send email to django...@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/751c367c-d5e9-e06b-8f5c-82054f11a9ab%40gmail.com.
For more options, visit 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.
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/16f42446-9507-4b07-adc9-31619e8a747b%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.