Django ORM to select with group by

Asked

Viewed 95 times

0

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?

1 answer

0

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()
  • Higor speaks. Thanks for the reply. But Unfortunately this query didn’t work either. It only brings the total of days..

  • Did it work? If not, let me know, or if you had to change something to work too.

  • It didn’t work no Higor. for.

  • I understand sometimes it’s hard to visualize how it would work without the project running.

  • Yes man... Very abstract

Browser other questions tagged

You are not signed in. Login or sign up in order to post.