Aggregation with filter on multiple annotations

Using Django Using the ORM
1

Dear group,

despite a lot of reading at Aggregation | Django documentation | Django, there are still a few things I couldn’t figure out.

I use these models:

class Document(models.Model):
    """Represents a document in the user's mailbox."""

    user = models.ForeignKey(
        User,
        models.PROTECT,
        help_text="The user that owns the document.",
    )

    path = models.CharField(
        max_length=80,
        help_text="The relative path into the file system.",
    )


class DocLogEntry(models.Model):
    """Records the most important events in the lifecycle of a document."""

    class Event(models.IntegerChoices):
        CREATED = 1, "The `Document` was created."
        FILE_ADDED = 2, "The related file was added."
        DL_REQUESTED = 3, "The document was requested for download."

    timestamp = models.DateTimeField(default=timezone.now)
    doc = models.ForeignKey(Document, on_delete=models.CASCADE)
    event = models.IntegerField(choices=Event)

That is, each user can have multiple documents, and for each document we record when it was created, when the related file was added, and whenever it was requested for download (“read”).

What I’d like to get is a queryset of a user’s documents (e. g. Document.objects.filter(user=userCarsten)), annotated with

  • the number of times the document was requested for download, and
  • the timestamp of the first such request.

Here is what I came up with:

from django.db.models import Count, Min, Q

def get_documents(user):
    docQs = Document.objects.filter(user=user)
    onlyDlRequ = Q(doclogentry__event=DocLogEntry.Event.DL_REQUESTED)

    return docQs.annotate(
        num_reads=Count("doclogentry", filter=onlyDlRequ),
        first_read=Min("doclogentry__timestamp", filter=onlyDlRequ),
    )

This works fine and I’m currently writing tests for it.

However, it seems awkward to repeat filter=onlyDlRequ for both annotations.
Is there a cleaner or more idiomatic way to express this?

At the end of section Filtering on annotations, there is a green note that suggests that there is a better way (“The aggregation filter argument is only useful when using two or more aggregations over the same relations with different conditionals.”), but I’ve not been able to infer what it might be, wondering whether I’m missing a simpler approach.

Note that I also need documents with 0 counts (unread documents), so filtering the base queryset like docQs.filter(doclogentry__event=DocLogEntry.Event.DL_REQUESTED).annotate(...) is not an option.

Thanks in advance for any insights!

2

As you’ve likely discovered constraining a NULLable relationship (that is the reverse of 1:N many relationship defined as DocLogEntry.doc) results in the LEFT OUTER JOIN being promoted to an INNER JOIN, that’s expected.

I you want the full set of objects with count that only relate to DL_REQUESTEDwithout repeating the Aggregate(filter) the only other option is to use a FilteredRelation which will preserve its NULLable → LEFT OUTER JOIN semantic.

Document.objects.alias(
    deleted_doc_log_entries=models.FilteredRelation(
        "doc_log_entries", condition=models.Q(
            doc_log_entries__event=DocLogEntry.Event.DL_REQUESTED
        )
    )
).annotate(
    num_reads=models.Count("deleted_doc_log_entries"),
    first_read=models.Min("deleted_doc_log_entries__timestamp"),
)

SELECT "app_document"."id",
       COUNT(deleted_doc_log_entries."id") AS "num_reads",
       MIN(deleted_doc_log_entries."timestamp") AS "first_read"
FROM "app_document"
LEFT OUTER JOIN "app_doclogentry" deleted_doc_log_entries ON (
    "app_document"."id" = deleted_doc_log_entries."doc_id"
    AND (deleted_doc_log_entries."event" = 3)
)
GROUP BY "app_document"."id"

You can try it out here.

Note that doing so will prevent JOIN re-use of the doc_log_entriesrelationship for any other purpose though.

3

Hello Simon,

thanks a lot for taking the time to look into this and for the alternative approach. It’s good to know that my initial solution wasn’t too far off. I appreciate the detailed explanation!