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
<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:
|
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.
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
<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.
To post to this group, send email to
django...@googlegroups.com.
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 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+unsubscr...@googlegroups.com
<mailto:django-users+unsubscr...@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/d367e365-414c-488c-ba76-a57ba42276b3%40googlegroups.com
<https://groups.google.com/d/msgid/django-users/d367e365-414c-488c-ba76-a57ba42276b3%40googlegroups.com?utm_medium=email&utm_source=footer>.
For more options, visit https://groups.google.com/d/optout.
--
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/d6f63f3f-2b89-1015-380f-a2a70a6d9bc0%40gmail.com.
For more options, visit https://groups.google.com/d/optout.
