1

Model , with abstract base class:

class MapObject(models.Model):
    start_date = models.DateTimeField(default= datetime.strptime('1940-09-01T00:00:00',  '%Y-%m-%dT%H:%M:%S'))
    end_date = models.DateTimeField(default= datetime.strptime('1941-07-01T00:00:00',  '%Y-%m-%dT%H:%M:%S'))
    description = models.TextField(blank=True)
    location = models.PointField()
    objects = models.GeoManager()
    user = models.ForeignKey(User)
    created = models.DateTimeField(auto_now_add = True)
    last_modified = models.DateTimeField(auto_now = True)
    source = models.ForeignKey(Source)
    address= models.TextField(blank=True, null=True)
    address_road = models.TextField(blank=True, null=True)

class Meta:
    abstract = True

class Bomb(MapObject, BombExtraManager):
    #Bomb Attributes
    type = models.CharField(choices= Type_CHOICES, max_length=10)
    night_bombing = models.BooleanField(blank=True)
    map_sheet = models.ForeignKey(MapSheet, blank=True, null=True)
    def __unicode__(self):
        return self.type

Now, I want to get the equivalent result using Django ORM as this query: 

Select date_part('day',"start_date") as "day", date_part('hour',"start_date") as "hour", Count('id')
from "Mapper_bomb"
where "source_id" = 1
group by date_part('hour',"start_date"), date_part('day',"start_date")
Order by date_part('day',"start_date") ASC, date_part('hour',"start_date") ASC

Which would give me a table with the count of bombs per day and hour.

Using Django ORM, I have come to the following at the moment (first_day is just a custom manager I defined that returns a subset of the data, same as source_id = 1):

Bomb.first_day.extra(select={'date': "date_part(\'day\', \"start_date\")", 'hour': "date_part(\'hour\', \"start_date\")"}).values('date', 'hour').order_by().annotate(Count('date'), Count('hour'))

but Django complains FieldError: Cannot resolve keyword 'date' into field. Is there a way using Django ORM to get the desired result or do I need to fallback on raw sql?

alukach
  • 5,921
  • 3
  • 39
  • 40
petzlux
  • 23
  • 1
  • 5

1 Answers1

7

Does this work?

Bomb.first_day.extra({
        'date': "date_part(\'day\', \"start_date\")",
        'hour': "date_part(\'hour\', \"start_date\")"
    }).values('date', 'hour').order_by('date', 'hour').annotate(Count('id'))
Pavel Anossov
  • 60,842
  • 14
  • 151
  • 124
  • Wow, that works, great! Altough, from the Django doc I gather you need to set the extra fields as select = ... , which you havent done here? Not sure what that means... – petzlux Nov 01 '12 at 17:42
  • The first parameter is `select`, whether you name it or not. – Pavel Anossov Nov 01 '12 at 18:36
  • 3
    This doesn't work in Django 1.5. Was it added since? `User.objects.extra({'started':'1'}).values('started').order_by('started').annotate(Count('started'))` gives `FieldError: Cannot resolve keyword 'started' into field.` – qris Jan 23 '14 at 13:05