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
FROM <model>
)
SELECT cte.id, cte.prior, cte.next
FROM <model> JOIN cte ON cte.id=<model>.id
WHERE id=<some_id>
From: django-users@googlegroups.com [mailto:django-users@googlegroups.com] On
Behalf Of Bernd Wechner
Sent: Thursday, March 15, 2018 4:48 PM
To: Django users
Subject: Re: Fetching next and/or prior objects given an arbitrary ordering
Mathtew,
Yes I most certainly have. I use them elsewhere. i love SubQuery. But I can
only find examples and documentation about using them in the WHERE clause (or a
filer() arguments). If you can write me an example of how one might be used in
the FROM clause as in my example below I'd be indebted. It's a challenge
indeed, as the FROM clause in the ORM is dictated by the model in the syntax
model.objects.filter(), where filter() defines the WHERE clause and model the
FROM clause. How can a SubQuiery be put into place there, in the FROM clause?
I would dearly love to learn that was possible and I've overlooked it somehow.
Regards,
Bernd.
On Friday, 16 March 2018 00:55:09 UTC+11, Matthew Pava wrote:
Did you look at the Subquery expression?
https://docs.djangoproject.com/en/2.0/ref/models/expressions/#subquery-expressions
From: django...@googlegroups.com<javascript:>
[mailto:django...@googlegroups.com<javascript:>] On Behalf Of Bernd Wechner
Sent: Thursday, March 15, 2018 1:57 AM
To: Django users
Subject: Re: Fetching next and/or prior objects given an arbitrary ordering
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...@googlegroups.com<javascript:>.
To post to this group, send email to djang...@googlegroups.com<javascript:>.
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<https://groups.google.com/d/msgid/django-users/b0748b45-7f1c-426a-82bb-e4b8bb5d950f%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<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/a1a431b8-2218-4a4a-aa21-e49670c4d131%40googlegroups.com<https://groups.google.com/d/msgid/django-users/a1a431b8-2218-4a4a-aa21-e49670c4d131%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/24da178862944f39954a72377ddfe4b7%40ISS1.ISS.LOCAL.
For more options, visit https://groups.google.com/d/optout.
