1

I've tried the following with no success:

Match.objects.filter(sendDate__gte=dateToStats).values("sendDate__day").annotate(perDay=Count("id")).order_by()

Fails with: 

Cannot resolve keyword 'sendDate__day' into field.

Where sendDate is a DateTime field, and dateToStats is just a certain date I'm filtering. I'm interested in having the number of matches per day (based on sendDate).

Thanks a lot guys!

Clash
  • 4,896
  • 11
  • 47
  • 67

1 Answers1

2

I don't think the __day mechanism used with filters (field__day) works with values. If your database has a function to extract the day from your date field you can do something like the snippet shown below. 

Match.objects.filter(sendDate__gte=dateToStats).extra(
    select = {"sendDate__day": "extract (day from sendDate)"})

Extract (day from sendDate) is specific to Postgresql. You will have to replace it with your database's equivalent.

Manoj Govindan
  • 72,339
  • 21
  • 134
  • 141
  • Ok, got it working with matchesPerDay = Match.objects.filter(sendDate__gte=dateToStats).extra(select={"dateDay": """DAY(sendDate)"""}).values("dateDay").annotate(perDay=Count("id")).order_by() How can I also have other fields of the model without grouping by it (values)? Thanks – Clash Sep 08 '10 at 12:36
  • 1
    I think you have got the concept of `values()` wrong. You can get all fields of the model _plus_ the extra by leaving out the `values()` part. i.e. `extra(**etc).annotate(**etc)`. See the documentation: http://docs.djangoproject.com/en/dev/ref/models/querysets/#values-fields – Manoj Govindan Sep 08 '10 at 12:40
  • Can you please enlighten me on how to group by? I thought it was via values(). Thanks! Ah, I think I got it via annotate – Clash Sep 08 '10 at 12:53
  • If you mean aggregation (sum, average etc.) then `annotate` is the way to go. There is no explicit "group by" in the SQL sense. See this SO question: http://stackoverflow.com/questions/629551/ – Manoj Govindan Sep 08 '10 at 12:56