Annotate with latest related object in Django

Question

I have a model Conversation and a model Message.

The model Message has a foreign key to conversation, a text field, and a date field.

How can I list all conversations and for each conversation get the most recent message and the date of the most recent message?

I guess it's something like

Conversation.objects.annotate(last_message=Max('messages__date'))

but it will only give me the latest date. I want last_message to contain both the text of the last message and the date it was created. Maybe I need to use prefetch_related?

Answer 1

Since Django 1.11, you could use Subqueries expressions:

latest_message = Subquery(Message.objects.filter(
    conversation_id=OuterRef("id"),
).order_by("-date").values('value')[:1])

conversations = Conversation.objects.annotate(
    latest_message=latest_message,
)

Answer 2

You could also could combine subquery with django.db.models.functions.JSONObject (added in Django 3.2) to fetch multiple fields at once without having to add multiple subqueries:

Conversation.objects.annotate(
    last_object=Message.objects.filter(conversation=OuterRef("pk"))
    .order_by("-date_created")
    .values(
        data=JSONObject(
            id="id", body="body", date_created="date_created"
        )
    )[:1]
)

Answer 3

Helper to annotate first or latest value using Subqueries expressions:

from django.db.models import OuterRef, Subquery
from django.db.models.query_utils import DeferredAttribute

def get_value_subquery(model, attr: str, ref: str, outref: str, order_by: str, **kwargs):
    """Annotation helper to annotate first value of related table to queryset
    attr - model attr which need to be attached
    ref - model attr by which field will be used for join with main table
    outref - as field_name (str) of main table to join with ref
    order_by - order by field
    kwargs - optional extra filtering kwargs for join    """

    db_field = model._meta.get_field(attr)
    ref_field = model._meta.get_field(ref)

    filters = {ref_field.attname: OuterRef(outref), **kwargs}
    sub_query = db_field.model.objects.filter(**filters).order_by(order_by)
    return Subquery(sub_query.values(db_field.attname)[:1], output_field=db_field.__class__())


def last_value_subquery(model, attr, ref, outref, **kwargs):
    return get_value_subquery(model, attr=attr, ref=ref, outref=outref, order_by=f"-{attr}", **kwargs)


def first_value_subquery(model, attr, ref, outref, **kwargs):
    return get_value_subquery(model, attr=attr, ref=ref, outref=outref, order_by=attr, **kwargs)

Models example

from django.db import models

class Customer(models.Model):
    name = models.TextField()

class CustomerVisit(models.Model):
    customer = models.ForeignKey(Customer, on_delete=models.CASCADE)
    datetime = models.DateTimeField()

Usage example

    def test_subquery(self):
        c = Customer.objects.create(name='a')
        first_visit = CustomerVisit.objects.create(customer=c, datetime=datetime.datetime(2010, 1, 1))
        second_visit = CustomerVisit.objects.create(customer=c, datetime=datetime.datetime(2011, 1, 1))
        third_visit = CustomerVisit.objects.create(customer=c, datetime=datetime.datetime(2013, 1, 1))

        main_query = Customer.objects.all()
        last_visit_q = last_value_subquery(CustomerVisit, attr='datetime', ref='customer', outref='id')
        first_visit_q = first_value_subquery(CustomerVisit, attr='datetime', ref='customer', outref='id')

        customer = main_query.annotate(last_visit=last_visit_q, first_visit=first_visit_q).get()
        assert customer.last_visit == third_visit.datetime
        assert customer.first_visit == first_visit.datetime

Answer 4

You can do it with prefetch_related and a queryset. Something like:

Conversation.objects.prefetch_related(
    Prefetch('messages'),
    queryset=Message.objects.order_by('date').first()
)