Nesting Django QuerySets

Question

Is there a way to create a queryset that operates on a nested queryset?

The simplest example I can think of to explain what I'm trying to accomplish is by demonstration.

I would like to write code something like

SensorReading.objects.filter(reading=1).objects.filter(meter=1)

resulting in SQL looking like

SELECT * FROM (
    SELECT * FROM SensorReading WHERE reading=1
) WHERE sensor=1;

---

More specifically I have a model representing readings from sensors

class SensorReading(models.Model):
    sensor=models.PositiveIntegerField()
    timestamp=models.DatetimeField()
    reading=models.IntegerField()

With this I am creating a queryset that annotates every sensor with the elapsed time since the previous reading in seconds

readings = (
    SensorReading.objects.filter(**filters)
    .annotate(
        previous_read=Window(
            expression=window.Lead("timestamp"),
            partition_by=[F("sensor"),],
            order_by=["timestamp",],
            frame=RowRange(start=-1, end=0),
        )
    )
    .annotate(delta=Abs(Extract(F("timestamp") - F("previous_read"), "epoch")))
)

I now want to aggregate those per sensor to see the minimum and maximum elapsed time between readings from every sensor. I initially tried

readings.values("sensor").annotate(max=Max('delta'),min=Min('delta'))[0]

however, this fails because window values cannot be used inside the aggregate.

Are there any methods or libraries to solve this without needing to resort to raw SQL? Or have I just overlooked a simpler solution to the problem?

Answer 1

Ended up rolling my own solution, basically introspecting the queryset to create a fake table to use in the creation of a new query set and setting the alias to a node that knows to render the SQL for the inner query

allows me to do something like

readings = (
    NestedQuery(
        SensorReading.objects.filter(**filters)
        .annotate(
            previous_read=Window(
                expression=window.Lead("timestamp"),
                partition_by=[F("sensor"),],
                order_by=[
                    "timestamp",
                ],
                frame=RowRange(start=-1, end=0),
            )
        )
        .annotate(delta=Abs(Extract(F("timestamp") - F("previous_read"), "epoch")))
    )
    .values("sensor")
    .annotate(min=Min("delta"), max=Max("delta"))
)

code is available on github, and I've published it on pypi

https://github.com/Simage/django-nestedquery

I have no doubt that I'm leaking the tables or some such nonsense still and this should be considered proof of concept, not any sort of production code.