Subquery django query to get biggest disctint values from objects and return those objects

So I have this query that I need to re-filter to get just the biggest taken_at field’s value from distinct meter id, but I cannot get the django orm’s/sql part to makes this query.

<QuerySet [<Reading: [127] meter installation: 29, type: 1, taken: 2019-10-07 16:06:48.101453+00:00 value: 78.0000, comment: , VAT: 22.00>, <Reading: [126] meter installation: 41, type: 2, taken: 2019-10-07 14:05:32.415905+00:00 value: 7.0000, comment: asdfe, VAT: None>, <Reading: [125] meter installation: 41, type: 2, taken: 2019-10-07 14:02:37.588983+00:00 value: 7.0000, comment: asdfe, VAT: None>, <Reading: [124] meter installation: 49, type: 2, taken: 2019-10-07 12:19:49.067398+00:00 value: 8.0000, comment: , VAT: 2.00>

this query contains lots of Reading objects, but I need to get just the biggest taken_at value from distinct meter installations, I’ve tried making annotation and then distinct , but they are not implemented together, I’m kinda new to SQL so any help would be great!

reading.py

class Reading(DateTrackedModel):
    meter_installation = models.ForeignKey(
        "MeterInstallation",
        on_delete=models.PROTECT,
        related_name="readings",
        null=False,
        blank=False,
        verbose_name=_("Meter Installation"),
    )
    value = models.DecimalField(
        decimal_places=4, max_digits=10, null=False, blank=False, default=0, verbose_name=_("Value")
    )
    price = models.DecimalField(
        decimal_places=4, max_digits=10, null=False, blank=False, default=0, verbose_name=_("Price")
    )
    reading_type = models.ForeignKey(
        "MeterType",
        on_delete=models.PROTECT,
        null=False,
        blank=False,
        related_name="readings",
        verbose_name=_("Reading type"),
    )
    comment = models.TextField(null=False, blank=True, verbose_name=_("Comment"))
    taken_at = models.DateTimeField(null=False, default=now, blank=False, verbose_name=_("Taken at"))
    VAT = models.DecimalField(decimal_places=2, max_digits=10, null=True, blank=True, verbose_name=_("VAT"))
    unit_name = models.CharField(max_length=100, null=False, blank=True, unique=False, verbose_name=_("Unit name"))
    unit_price = models.DecimalField(
        decimal_places=4, max_digits=10, null=False, blank=False, default=0.0, verbose_name=_("Unit price")
    )

MeterInstallation model:

class MeterInstallation(ActiveAfterUntilModel, DateTrackedModel, MPTTModel, NamedModel):  # type: ignore
    meter_type = models.ForeignKey(
        MeterType,
        on_delete=models.PROTECT,
        null=False,
        blank=False,
        related_name="installations",
        verbose_name=_("Meter Installation type"),
    )
    parent = TreeForeignKey(
        "self", on_delete=models.CASCADE, null=True, blank=True, related_name="children", db_index=True
    )
    meter = models.ForeignKey(
        Meter, on_delete=models.PROTECT, related_name="installations", null=False, blank=False, verbose_name=_("Meter")
    )
    building = models.ForeignKey(
        Building,
        on_delete=models.PROTECT,
        related_name="meter_installations",
        null=True,
        blank=False,
        verbose_name=_("Building"),
    )
    places = models.ManyToManyField(Place, related_name="meter_installations", blank=False, verbose_name=_("Places"))
    initial_reading = models.DecimalField(
        decimal_places=4, max_digits=10, null=False, blank=False, default=0, verbose_name=_("Initial reading")
    )
    final_reading = models.DecimalField(
        decimal_places=4, max_digits=10, null=True, blank=True, verbose_name=_("Final reading")
    )

Answer

It’s not very clear what’s the current status of your Reading QuerySet, but the general way of doing what you want can be found here in the docs. In your case it should be something like this:

reading_qs.values('meter_installation').annotate(max_taken_at=models.Max('taken_at'))

UPDATE:

So it wasn’t very clear from the first post, but you are having the greatest-n-per-group problem. (In your case n=1)

One way to approach this specific version of the problem is via window query (if your database supports it). It should be something like this:

    reading_qs.annotate(max_taken_at=Window(
        expression=Max('taken_at'),
        partition_by=F('meter_installation')
    )).filter(max_taken_at=F('taken_at'))

Update: This actually won’t work, because Window annotations are not filterable. I think in order to filter on window annotation you need to wrap it inside a Subquery, but with Subquery you are actually not obligated to use a Window function, there is another way to do it, which is my next example.

Another approach would be via subquery it will look like:

reading_qs.annotate(
    max_taken_at=Subquery(
            reading_qs.filter(meter_installation=OuterRef('meter_installation'))
            .values('meter_installation')
            .annotate(max_taken_at=Max('taken_at'))
            .values('max_taken_at')
    )
).filter(max_taken_at=F('taken_at'))

A third solution, which would be PostgreSQL only solution would be:

reading_qs.order_by(
    'meter_installation', '-taken_at'
).distinct('meter_installation')