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+unsubscribe@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.
class Thing(models.Model):
field1 = ...
field2 = ...
field2 = ...
class Meta:
ordering = ['field1', '-field2', 'field3']
ORDER BY field1 ASC, field2 DESC, field3 ASC
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.
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.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.
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:
http://sqlblog.com/blogs/michael_zilberstein/archive/2012/03/14/42332.aspx
Regards,
Bernd.
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...@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/d367e365-414c-488c-ba76-a57ba42276b3%40googlegroups.com.
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
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...@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/d367e365-414c-488c-ba76-a57ba42276b3%40googlegroups.com.
SELECT *
FROM (
SELECT id, LAG(id, 1) OVER (ORDER BY <an order_by expression>) AS prior, LEAD(id 1) OVER (ORDER BY <an order_by expression>) AS next
FROM <mytable>
) result
WHERE id=<myid>;
result = model.objects.annotate(prior=Window(expression=Lag("pk"), order_by=order_by)).annotate(next=Window(expression=Lead("pk"), order_by=order_by))
SELECT id, LAG(id, 1) OVER (ORDER BY <an order_by expression>) AS prior, LEAD(id 1) OVER (ORDER BY <an order_by expression>) AS next
FROM <mytable>
WHERE id=<myid>;
def get_prior(model, pk):
# Get the ordering list for the model (a list of fields
# See: https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
ordering = model._meta.ordering
order_by = []
for f in ordering:
if f.startswith("-"):
order_by.append(F(f[1:]).desc())
else:
order_by.append(F(f).asc())
my_queryset = model.objects.annotate(prior=Window(expression=Lag("pk"), order_by=order_by))
my_result = Model(my_queryset).objects.filter(pk=pk)
my_result = my_queryset.filter(pk=pk)
select * from my_queryset where pk=pk
def get_neighbor_pks(model, pk, filterset=None, ordering=None):
'''
Given a model and pk that identify an object (model instance) will, given an ordering
(defaulting to the models ordering) and optionally a filterset (from url_filter), will
return a tuple that contains two PKs that of the prior and next neighbour in the list
either of all objects by that ordering or the filtered list (if a filterset is provided)
:param model: The model the object is an instance of
:param pk: The primary key of the model instance being considered
:param filterset: An optional filterset (see https://github.com/miki725/django-url-filter)
:param ordering: An optional ordering (otherwise default model ordering is used). See: https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
'''
# If a filterset is provided ensure it's of the same model as specified (consistency).
if filterset and not filterset.Meta.model == model:
return None
# Get the ordering list for the model (a list of fields
# See: https://docs.djangoproject.com/en/2.0/ref/models/options/#ordering
if ordering is None:
ordering = model._meta.ordering
order_by = []
for f in ordering:
if f.startswith("-"):
order_by.append(F(f[1:]).desc())
else:
order_by.append(F(f).asc())
# Define the window functions for each neighbour
window_lag = Window(expression=Lag("pk"), order_by=order_by)
window_lead = Window(expression=Lead("pk"), order_by=order_by)
# Get a queryset annotated with neighbours. If annotated attrs clash with existing attrs an exception
# will be raised: https://code.djangoproject.com/ticket/11256
try:
# If a non-empty filterset is supplied, respect that
if filterset and filterset.filter:
qs = filterset.filter() | model.objects.filter(pk=pk).distinct()
# Else we just use all objects
else:
qs = model.objects
# Now annotate the querset with the prior and next PKs
qs = qs.annotate(neighbour_prior=window_lag, neighbour_next=window_lead)
except:
return None
# Finally we need some trickery alas to do a query on the queryset! We can't add this WHERE
# as a filter because the LAG and LEAD Window functions fail then, they are emoty because
# there is no lagger or leader on the one line result! So we have to run that query on the
# whole table.then extract form the result the one line we want! Wish I could find a way to
# do this in the Django ORM not with a raw() call.
ao = model.objects.raw("SELECT * FROM ({}) ao WHERE {}=%s".format(str(qs.query), model._meta.pk.name),[pk])
if ao:
return (ao[0].neighbour_prior,ao[0].neighbour_next)
else:
raise None
Did you look at the Subquery expression?
https://docs.djangoproject.com/en/2.0/ref/models/expressions/#subquery-expressions
--
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/b0748b45-7f1c-426a-82bb-e4b8bb5d950f%40googlegroups.com.
To post to this group, send email to djang...@googlegroups.com.
Hi Bernd,
Indeed, I did not look closely at what was happening. Definitely add a feature request. I sincerely believe that if we would implement the Subquery object as a CTE, it would resolve these situations without much changing anything else. https://code.djangoproject.com/ticket/28919
Thanks,
Matthew
I would like to see something like this:
cte = Subquery(model.objects.annotate(prior=Window(expression=Lag("pk"), order_by=order_by)).annotate(next=Window(expression=Lead("pk"), order_by=order_by)))
result = model.objects.annotate(id=cte["id"], prior=cte["prior"], next=cte["next"]).filter(pk=some_id)
would produce something like this
WITH cte(id, prior, next) AS (
SELECT id, LAG(id, 1) OVER (ORDER BY <an order_by expression>) AS prior, LEAD(id 1) OVER (ORDER BY <an order_by expression>) AS next
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/a1a431b8-2218-4a4a-aa21-e49670c4d131%40googlegroups.com.