Annotate a QuerySet with the latest value from a filtered join

Question

How can I, in a single expression, get the latest value from the filtered results of a many-to-one relationship to annotate a Django QuerySet?

Given this toy schema:

from django.db import models

class Lorem(models.Model):
    """ Lorem ipsum, dolor sit amet. """

class LoremStatusEvent(models.Model):
    """ A status event change on a given `ipsum` for a `lorem`. """

    created = models.DateTimeField(auto_now_add=True)
    lorem = models.ForeignKey(Lorem)
    ipsum = models.CharField(max_length=200)
    status = models.CharField(max_length=10)

For the purpose of making a custom QuerySet in a Django LoremAdmin (for the Lorem model), I need to:

Derive foo_status and bar_status each from separate JOIN clauses to a LoremStatusEvent model: lorem.loremstatusevent_set__status.
Filter each join to only include those events which are for the corresponding Ipsum value: foo_status_events=LoremStatusEvent.filter(ipsum='foo').
Aggregate the set of status events to only the latest for each corresponding join: foo_status=LoremStatusEvent.objects.filter(ipsum='foo').latest('created').status.
Annotate the results with a couple of extra values, foo_status and bar_status: queryset = queryset.annotate(foo_status=???).annotate(bar_status=???).

If I invent some functions to do all this – get_field, latest_by, filter_by, loremstatus_set_of_every_corresponding_lorem – I could write the admin something like this:

from django.contrib import admin

class LoremAdmin(admin.ModelAdmin):
    """ Django admin for `Lorem` model. """

    class Meta:
        model = Lorem

    def get_queryset(request):
        """ Get the `QuerySet` of all instances available to this admin. """
        queryset = super().get_queryset(request)
        queryset.annotate(
            foo_status=(
                get_field('status')(
                    latest_by('created')(
                        filter_by(ipsum='foo')(
                            loremstatusevent_set_of_every_corresponding_lorem)))),
            bar_status=(
                get_field('status')(
                    latest_by('created')(
                        filter_by(ipsum='bar')(
                            loremstatusevent_set_of_every_corresponding_lorem)))),
        )
        return queryset

What actual functions should replace each of these placeholder names?

loremstatus_set_of_every_corresponding_lorem, the equivalent of Lorem.loremstatusevent_set.
filter_by, to filter a join at the right side.
latest_by, to aggregate the result set, ordered by a field, to get the single latest instance. I don't see any such documented aggregation function.
get_field, to reference a field from the resulting instance.

Remember that all this needs to be done on a queryset of Lorem instances, accessing the related instances via Lorem.loremstatusevent_set; I don't have a LoremStatusEvent instance at that point, so I can't directly use attributes of LoremStatusEvent.

So, what actual Django functionality should be in the above placeholders?

See also: https://stackoverflow.com/questions/31234880/annotate-with-value-of-latest-related-in-django-1-8-using-conditional-annotation — Risadinha, Oct 04 '17 at 10:36

score 10 · Accepted Answer · edited Sep 20 '17 at 02:40

10

You should be able to do this with the new Subquery functionality, which is also available as a backport to 1.8+.

try:
    from django.db.models.expressions import Subquery, OuterRef
except ImportError:
    from django_subquery.expressions import Subquery, OuterRef

class LoremAdmin(admin.ModelAdmin):
    # …

    def get_queryset(request):
        queryset = super().get_queryset(request)
        status_event_per_lorem = LoremStatusEvent.objects.filter(
            lorem=OuterRef('pk'))
        latest_status_event_per_lorem = (
            status_event_per_lorem.order_by(
                'pk', '-created').distinct('pk'))
        latest_status_event_for_ipsum_foo = (
            latest_status_event_per_lorem.filter(ipsum='foo'))
        latest_status_event_for_ipsum_bar = (
            latest_status_event_per_lorem.filter(ipsum='bar'))
        queryset = queryset.annotate(
            foo_status=Subquery(
                latest_status_event_for_ipsum_foo.values('status')[:1]), 
            bar_status=Subquery(
                latest_status_event_for_ipsum_bar.values('status')[:1]), 
        )

        return queryset

edited Sep 20 '17 at 02:40

bignose

30,281
14
77
110

answered May 10 '17 at 04:05

Matthew Schinckel

35,041
6
86
121

That raises the error `ValueError: This queryset contains a reference to an outer query and may only be used in a subquery.` I think that's because the `filter(…)` result is then used for `latest(…)`. – bignose May 10 '17 at 05:35
1

I have found a way to do this which doesn't cause that error; I am editing this answer so it works as intended. – bignose May 11 '17 at 00:54
[Annotation helper to annotate last value of related table](https://stackoverflow.com/a/71621283/1731460) – pymen Mar 25 '22 at 18:09
This solution appends multiple subqueries, see my answer here to get the whole object in just one subquery (Django 3.2+): https://stackoverflow.com/a/73088863/6063532 – suayip uzulmez Jul 23 '22 at 07:38

Annotate a QuerySet with the latest value from a filtered join

1 Answers1

Linked