-1

I'm trying to convert a working SQL query to a Django ORM statement.

For every location I want to retrieve the most popular date based on the sum of all activities for each date+location.

Django models:

class Location(models.Model):
    slug = models.SlugField(unique=True)

class Raw(models.Model):
    location = models.ForeignKey('Location', related_name='raw')
    activities = models.PositiveIntegerField(db_index=True)
    date = models.DateField(db_index=True)

Data set:

    date    | activities | location_slug    
------------+------------+-----------
 2015-07-02 |        234 | london
 2015-07-07 |        100 | london
 2015-07-02 |         51 | london
 2015-07-02 |         12 | melbourne
 2015-07-02 |        111 | melbourne
 2015-07-01 |       1234 | berlin
 2015-07-01 |         12 | berlin

Working SQL.

SELECT DISTINCT ON (slug) date, slug, SUM(activities)
FROM core_raw, core_location
WHERE core_location.id = core_raw.location_id
GROUP BY date, slug
ORDER BY slug, sum DESC;

Result:

    date    |   slug    | sum  
------------+-----------+------
 2015-07-01 | berlin    | 1246
 2015-07-02 | london    |  285
 2015-07-02 | melbourne |  123

How can I do this with the Django ORM? Is it even possible?!

bradley
  • 776
  • 2
  • 11
  • 29

1 Answers1

0

This can help.

from django.db.models import Sum

values = Raw.objects.all().values('location_slug','date').annotate(data_sum=Sum('activities')).distinct()
kartikmaji
  • 946
  • 7
  • 22
  • @bradley see for any one location say london, there are 3 different dates. So, it might create a problem. – kartikmaji Jul 07 '15 at 21:45
  • Yes I need the date+location with the highest sum of activities, for that particular date+location. So - London, 2015-07-02, 285 would be a row, as the sum activities, from London, on 2015-07-02 is 285. I then only need the row for each location with the highest sum of activities. – bradley Jul 07 '15 at 21:47
  • I think you would need to use .distinct('location__slug', 'date') otherwise due to data_sum the rows would be different? And if so it would be affected by the "annotate + distinct(field) NotImplementedError" - http://stackoverflow.com/questions/11831510/django-using-annotate-count-and-distinct-on-a-queryset – bradley Jul 07 '15 at 22:07
  • yes, may be try it once. If that works, well and good congo :) – kartikmaji Jul 07 '15 at 22:09
  • Yes, it throws "NotImplementedError: annotate() + distinct(fields) is not implemented." – bradley Jul 08 '15 at 08:09