'Annotate Total Created, Modified, and Deleted for each year

Given a Model Item how would I find the total number of items created, modified, and deleted every year? And can this be done in a single database query?

from django.db import models

class Item(models.Model):
    created_at = models.DateTimeField(null=True, blank=True)
    modified_at = models.DateTimeField(null=True, blank=True)
    deleted_at = models.DateTimeField(null=True, blank=True)

My current query returns the same counts for total_created, total_modified, and total_deleted.

from django.db.models.functions import ExtractYear

Item.objects.annotate(
    created_at_year=ExtractYear("created_at"),
    modified_at_year=ExtractYear("modified_at"),
    deleted_at_year=ExtractYear("deleted_at"),
).values("created_at_year", "modified_at_year", "deleted_at_year").annotate(
    total_created=models.Count("id"),
    total_modified=models.Count("id"),
    total_deleted=models.Count("id"),
)

Example Data

id created_at modified_at deleted_at
1 2020-01-01 01:01:01 2020-01-01 01:01:01 2021-01-01 01:01:01
2 2020-01-01 01:01:01 2021-01-01 01:01:01 2021-01-01 01:01:01
3 2020-01-01 01:01:01 2021-01-01 01:01:01 2022-01-01 01:01:01

Desired Output

{
    2020: {"total_created": 3, "total_modified": 1, "total_deleted": 0},
    2021: {"total_created": 0, "total_modified": 2, "total_deleted": 2},
    2022: {"total_created": 0, "total_modified": 0, "total_deleted": 1},
}

I know it's possible to add filter parameter to Count but I do not know if I can use that here.



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source