Django ORM to select with group by

Question

I have a very simple model in Django:

class TrackService(models.Model):
    ip = models.CharField(max_length=150, db_column='nm_grupo')
    creation_date = models.DateField(auto_now_add=True,auto_created=True, db_column='dt_criacao')

In this model I save an ip and date each time a user performs a certain function. Now I want to know how many times each ip performed the function, separated per day.

For example:

{ 'data': '14/08/2019', 'acesso_total': '10',
  'cada_ip': {
       '192.168.1.10': '7',
       '192.168.1.11': '3'
   },
   'data': '13/08/2019', 'acesso_total': '15',
  'cada_ip': {
       '192.168.1.10': '5',
       '192.168.1.11': '5',
       '192.168.1.12': '5'
   },
   ...
}

The closest I got was:

teste = TrackService.objects.values('creation_date').annotate(date_count=Count('creation_date'))
for t in teste:
    print(t)
    ips_contador = TrackService.objects.filter(creation_date=t.get('creation_date')).values('creation_date', 'ip').annotate(date_count=Count('ip'))
    for ip in ips_contador:
        print(ip)

That answers:

{'creation_date': datetime.date(2019, 8, 13), 'date_count': 3}
{'creation_date': datetime.date(2019, 8, 13), 'ip': '192.168.1.8', 'date_count': 1}
{'creation_date': datetime.date(2019, 8, 13), 'ip': '192.168.1.9', 'date_count': 2}
{'creation_date': datetime.date(2019, 8, 14), 'date_count': 10}
{'creation_date': datetime.date(2019, 8, 14), 'ip': '127.0.0.1', 'date_count': 6}
{'creation_date': datetime.date(2019, 8, 14), 'ip': '192.168.1.12', 'date_count': 2}
{'creation_date': datetime.date(2019, 8, 14), 'ip': '192.168.1.13', 'date_count': 1}
{'creation_date': datetime.date(2019, 8, 14), 'ip': '192.168.1.9', 'date_count': 1}

But I wanted to avoid this for. What is the most efficient way?

Answer 1

I think you could use it aggregate right after your annontate as shown in documentation.

In your Model you would need to implement the class Meta and declare the ordering.

py.models

class TrackService(models.Model):
    ip = models.CharField(max_length=150, db_column='nm_grupo')
    creation_date = models.DateField(auto_now_add=True,auto_created=True, db_column='dt_criacao')

    class Meta:
        ordering = ['-creation_date']

In case your query would look something like:

py views.

TrackService.objects.values('creation_date').annotate(date_count=Count('creation_date')).aggregate(Sum('date_count')).order_by()