2

I have a django model with a DateTimeField.

class Point(models.Model):
    somedata = models.CharField(max_length=256)
    time = models.DateTimeField()

I want to get a count of the number of these objects for each day. I can do this with the following SQL query, but don't know how to do it through django.

SELECT DATE(`time`), Count(*)
FROM `app_point`
GROUP BY DATE(`time`)

Being able to restrict the results to a date range would also be good.

Keith
  • 924
  • 6
  • 14
  • 1
    Check out http://stackoverflow.com/questions/327807/django-equivalent-for-count-and-group-by – T. Stone Apr 29 '10 at 03:16
  • I saw that question, but there they are doing a GROUP BY on a field. I need to do a GROUP BY on a function on a field, not the field itself. – Keith Apr 29 '10 at 04:26

1 Answers1

3

Try this,

from django.db.models.sql.aggregates import Aggregate
from django.db.models import Count

class Day(Aggregate):
    """Custom aggregator
    """
    sql_function = 'DATE'
    sql_template = "%(function)s(%(field)s)"

    def __init__(self, lookup, **extra):
        self.lookup = lookup
        self.extra = extra

    def _default_alias(self):
        return '%s__%s' % (self.lookup, self.__class__.__name__.lower())
    default_alias = property(_default_alias)

    def add_to_query(self, query, alias, col, source, is_summary):
        super(Day, self).__init__(col, source, is_summary, **self.extra)
        query.aggregate_select[alias] = self

    q = Point.objects.annotate(day=Day('time')).annotate(found=Count('time')).values('day', 'found')
    # custom group by
    q.query.group_by = ["day"]

    print q
    # should return something like
    [{'found': 6, 'day': datetime.date(2010, 4, 30)}, 
     {'found': 4, 'day': datetime.date(2010, 5, 1)}, 
     {'found': 3, 'day': datetime.date(2010, 5, 2)}]

According to this post the above code would probably work only with MySql database.

vinilios
  • 871
  • 7
  • 15