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 some very specific function LAG and LEAD which I can see Postgresql, MySQL, MS-SQL supports these by SQLlite does not (perhaps not a crisis as I expect we could ignore SQLlite in what I implement).

LAG and LEAD are analytic functions that provide access to precisely what I want, the prior and next tuple(s).

But you can using them write something like this (in pro forma):

SELECT  *,  LAG(id) over (order by ...) as prior, LEAD(id) over (order by ...) as next
FROM table
WHERE id = myid

This will produce one tuple which has two new columns, prior and next, which contain the id of the prior and next tuples to that with myid following the specified order by.

Alas it uses two analytic functions I'm not sure the ORM supports. I think if not there's a fair case to put to Django to implement this as it's a fairly ordinary use case (finding neighboring objects in the models ordering).

There may be a way to replicate LAG and LEAD using self joins, with even better performance:

https://dba.stackexchange.com/questions/158374/performance-comparison-between-using-join-and-window-function-to-get-lead-and-la/158429

http://sqlblog.com/blogs/michael_zilberstein/archive/2012/03/14/42332.aspx

Regards,

Bernd.

On 2/03/2018 3:48 AM, C. Kirby wrote:

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 want for us to help you think about it?

On Wednesday, February 28, 2018 at 6:50:56 PM UTC-5, 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:

class Thing(models.Model):
     field1 = ...
     field2 = ...
     field2 = ...
     class Meta:
         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.

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> 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 a topic in the Google Groups "Django users" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/django-users/pVJH7MYOzuA/unsubscribe.
To unsubscribe from this group and all its topics, 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/d367e365-414c-488c-ba76-a57ba42276b3%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.