Well the silence was stunning, so I pottered along and have a working
solution. Alas I lean on a raw() call which I'd rather not. If there is any
way to do this in the ORM I'd love to know. Otherwise I'm probably off to
submit it as a feature request. The Window functions will all suffer this
utility problem, that to use them meaningfully it may often be necessary to
nest SELECTs. Here's a working neighbour fetcher:
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
On Wednesday, 14 March 2018 06:28:55 UTC+11, Bernd Wechner wrote:
>
> Hmmm, really stuck on this. Can find no way of selecting from a select in
> the ORM. The whole premise seems be to start from model.objects and add SQL
> clauses with filter, annotate and other methods. But a QuerySet is not a
> model and queryset.objects doesn't exist, and queryset.filter just adds a
> where clause to the select on the original model not on the result of the
> queryset. It's almost as if we need a way to cast a queryset as a virtual
> model in the style of:
>
> 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)
>
> That last line is the crux of the issue.I see no way of writing this. When
> doing this:
>
> my_result = my_queryset.filter(pk=pk)
>
> The WHERE clause ends up on the select in in my_querset. we want to wrap
> the whole of my_queryset with
>
> select * from my_queryset where pk=pk
>
> But how? Can anyone think of a way to do that?
>
> Regards,
>
> Bernd.
>
> On Monday, 12 March 2018 23:43:33 UTC+11, Bernd Wechner wrote:
>>
>> OK Trying to implement this now and has SQL that works but can't work how
>> to use the Django ORM to produce it. Here is the proforma SQL:
>>
>> 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>;
>>
>> There's a sub query involved (as LAG and LEAD don't work if you constrain
>> the inner query alas. And I've used SubQuery in Django before but not like
>> this, (in the FROM clause), and am a tad stuck. Can anyone code this sort
>> of query up in the Django ORM with QuerySets.
>>
>> I can create the inner set.
>>
>> result = model.objects.annotate(prior=Window(expression=Lag("pk"),
>> order_by=order_by)).annotate(next=Window(expression=Lead("pk"), order_by=
>> order_by))
>>
>> Now the question is how to filter() the result of that, rather than that
>> itself ;-). If that makes sense. Namely the aforementioned SQL. Any
>> filter() I add to the end of this ORM QyerySet produces SQL more like
>>
>> 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>;
>>
>> In with prior and next are empty, because that's just how such SQL works
>> it seems. do the WHERE on the table produced by the SELECT/FROM as per SQL
>> above to make this work.
>>
>> 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+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/b0748b45-7f1c-426a-82bb-e4b8bb5d950f%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
