Django Annotate Not Grouping Unless I Add Values(

Question!

Python 3 & Django 1.5.1.

I want to get a QuerySet object, that groups people by a date. The date is pulled in from a ForeignKey, so if I use values() in the query, it doesn't return the date but only the id # that is the actual key. If I don't use values(), the query just won't group. Now, as I understand it, annotate(), is the way to group a QuerySet of stuff.

So is there a way to "group by" without using values()?

I wouldn't care about using values(), but it forces me to use extra queries (thus more overhead on the server) just to get the date's.

models --

class Event(models.Model):
    day = models.DateField()
    blah = models.TextField()

class Worker(models.Model):
    eid = models.ForeignKey('Events')
    name = models.CharField(max_length=50)
    hours = models.TimeField()
    job_title = models.CharField(max_length=20)

What I want to display, looks like so --

Name | Days Worked | Hours | Last Worked
-----------------------------------------
Dan  |      4      | 30:00 | 2013-06-10
Ted  |      3      | 22:00 | 2013-06-09
Bill |      2      | 16:00 | 2013-06-11
-----------------------------------------

But the best I'm getting, is displaying the ForeignKey --

ww = Worker.objects.filter(eid__day__year=year,eid__day__month=month
                           ).values('name','job_title').annotate(
                               whours=Sum('hours'),
                               wcount=Count('name'),
                               recent=Max('eid'),).order_by(
                               '-wcount','-recent','hours','name')

Name | Days Worked | Hours | Last Worked
-----------------------------------------
Dan  |      4      | 30:00 |      5
Ted  |      3      | 22:00 |      2
Bill |      2      | 16:00 |      8
-----------------------------------------

Or, getting the date & no groupings when I try to just get the object --

ww = Worker.objects.filter(eid__day__year=year,eid__day__month=month
                           ).annotate(
                               whours=Sum('hours'),
                               wcount=Count('name'),
                               recent=Max('eid'),).order_by(
                               '-wcount','-recent','hours','name')

Name | Days Worked | Hours | Last Worked
-----------------------------------------
Ted  |      1      | 08:00 | 2013-06-07
Dan  |      1      | 08:00 | 2013-06-10
Ted  |      1      | 04:00 | 2013-06-03
Dan  |      1      | 08:00 | 2013-06-06
Bill |      1      | 08:00 | 2013-06-09
Ted  |      1      | 10:00 | 2013-06-01
Dan  |      1      | 06:00 | 2013-06-02
Dan  |      1      | 08:00 | 2013-06-04
Bill |      1      | 08:00 | 2013-06-11
-----------------------------------------

I'm guessing there must be a way to group, without forcing use of the plain values and extra queries, but I'm not understanding how to do it. This would be easy to do in plain MySQL, but I have to make this work in PostgreSQL and MySQL, so I need to use the Django way of doing the query.

By : Zamphatta


Answers

Finally figured it out. I just had to add __day to the value of recent.

ww = Worker.objects.filter(eid__day__year=year,eid__day__month=month
                       ).values('name','job_title').annotate(
                           whours=Sum('hours'),
                           wcount=Count('name'),
                           recent=Max('eid__day'),).order_by(
                           '-wcount','-recent','hours','name')

For future refence, if anyone's needing to grab the human readable part of a model's choices tuple (ya know, the get_

By : Zamphatta


This video can help you solving your question :)
By: admin