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/dace1caf-4748-4d99-a836-7f016f87414d%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
