'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 |
|---|
