jeudi 25 juin 2015

Unable to group sum value in Django query

I have a Django model that looks like this (I've trimmed a few details for brevity):

class Resource(models.Model):
    role = models.ForeignKey('Role')
    project = models.ForeignKey('Project')
    start_date = models.DateField
    percentage = models.DecimalField

I've got a bunch of rows in this table with various percentage values, and I want to sum them up for each unique start_date. In playing in the database by hand, I found that the SQL to do this is:

SELECT start_date, SUM(percentage) AS "total"
FROM myapp_Resource
GROUP BY start_date

When I try to use the Django machinery to get a similar query, I inevitably get the wrong thing. I would think that the following code would give me what I want:

Resource.objects.values('start_date').annotate(total = Sum('percentage'))

Unfortunately, it returns the following query:

SELECT start_date, SUM(percentage) AS "total"
FROM myapp_resource
INNER JOIN myapp_role ON ( myapp_resource.role_id = myapp_role.id )
GROUP BY start_date, myapp_role.name
ORDER BY myapp_role.name ASC

Where is this inner join coming in? Nowhere am I explicitly indicating that there's a join; I'm not even interested in that column! Are my ForeignKeys biting me somehow? I've tried variations of annotate, but to no avail. Any pointers would be greatly appreciated.

Aucun commentaire:

Enregistrer un commentaire