Number of objects over the time

[plush niche]

Hello everyone,

I am wandering if there is an easy way to build graphics showing evolution a the number of objects over the time.

Here are my models :

class Category(models.Model):
  name = models.CharField(max_length=255)

class Incident(models.Model):
  title = models.CharField(max_length=255, null=False)
  start_date = models.DateTimeField()
  resolving_date = models.DateTimeField()
  category = models.ForeignKey(Category)

I made a view to retrieve, for the last 30 days, the number of incidents by category :

class IncidentByCategoryOverTimeView(APIView):
  def by_category(self, incidents, categories, single_date):
    slot_max = single_date.replace(hour=23, min=59, second=59, microsecond=999999)
    by_category={}
    by_category['name'] = date.strftime(single_date, "%d-%m-%Y")
    for category in categories:
      by_category[category.name] = incidents.filter(category__name=category.name).filter(Q(start_date__lt=slot_max, resolving_date__gt=slot_max)).distinct().count()
    return by_category

  def get(self, request, *args, **kwargs):
    frame = 30
    incidents = Incident.objects.all()
    stats = []
    categories = Categories.objects.all()

    for single_date in ((timezone.now() - timedelta(frame - 1) + timedelta(n)).replace(tzinfo=timezone.gett_current_timezone(), hour=0, min=0, second=0, microsecond=0) for n in range(frame)):
      stats.append(self.by_category(incidents, categories, single_date))
    return response.Response(stats, status=status.HTTP_200_OK)

The output will be something like :

[
  {
    "name": "20-09-2023"
    "Critical": 10,
    "Medium": 15,
  },
  {
    "name": "21-09-2023"
    "Critical": 9,
    "Medium": 10,
  },
  ...
]

Do you think there is a better way to do that ?

[plush niche]

Hello everyone, I am now thinking about another way to solve my problem : write the stats of each day in the database instead of compute them. It consume more space in my database but it spends less time to retrieve them. Do you think it is a better way ?

[atomic pasture]

caching is certainly an option

I would wait until it actually starts to be slow, though

[lucid plaza]

I think you should look at annotations and maybe even window functions. With this setup, you're executing a db query for every category for each day, for 30 days. So if you have 10 categories, that's 300 queries. Not great.

I haven't used them personally, but I think they may be the answer https://docs.djangoproject.com/en/4.2/ref/models/expressions/#window-functions

[plush niche]

Thank you for your answers @lucid plaza @atomic pasture !

[final stone]

@plush niche Sorry for the late answer, but if you only want the number of incidents by day do the cumulation in Python code something like Incident.objects.annotate(_day=Trunc('start_date', 'day')).values('_day', 'category__name').annotate(count=Count("*")) may just work.

If you also want cumulative counts I myself drop down to raw SQL. I find the ORM exceptionally well implemented and helpful but once you want to use CTEs (common table expressions) it's easier for me to write the SQL by hand into a file and repeatedly pipe it into psql for testing.

[plush niche]

Hello Matthias. Thank you for your answer. But how I can give a date, and check that my incident have a start date lower than this date, and a resolving date higher than this date ? I can do my filer before doing your anotation ?