Opened 9 years ago

Closed 4 years ago

#25534 closed New feature (fixed)

Allow using transforms in aggregates.

Reported by: Raúl Pedro Santos Owned by: Ian Foote
Component: Database layer (models, ORM) Version: dev
Severity: Normal Keywords:
Cc: josh.smeaton@…, info+coding@… Triage Stage: Ready for checkin
Has patch: yes Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description (last modified by Raúl Pedro Santos)

I've been scouring the web for an answer to this but the closest I can find is #25339, which is almost what I need but not quite, so I think I can safely conclude that it is just not possible with the currently available functionality and thus I'm opening this ticket to suggest adding it.

I would like to be able to use datetime lookups in an aggregate() call on a QuerySet.

My specific use case is this: I have a set of electricity consumption readings, each with a datetime field (and a few others). I need to sum the consumption and cost values grouped by month, day, year, week, etc. In other words, I need to be able to get the total energy consumption value and corresponding cost for each month, day, year, week, etc.

This is my ElectricityReading model and its parent Reading model (separated because we also have consumption readings for water and gas, which also derive from Reading):

from model_utils.models import TimeStampedModel
# Other imports here...

class Reading(TimeStampedModel):
    device = models.ForeignKey(Device)
    datetime = models.DateTimeField() # Terrible property name, I know :)
    manual = models.BooleanField(default=False)
    inserted_by = models.ForeignKey(User)

    class Meta:
        abstract = True

class ElectricityReading(Reading):
    vph1 = models.DecimalField(max_digits=18, decimal_places=3, null=True)
    vph2 = models.DecimalField(max_digits=18, decimal_places=3, null=True)
    vph3 = models.DecimalField(max_digits=18, decimal_places=3, null=True)
    wh_imp = models.DecimalField(max_digits=18, decimal_places=3)
    varh = models.DecimalField(max_digits=18, decimal_places=3, null=True)
    pf = models.DecimalField(max_digits=18, decimal_places=3, null=True)

    price = models.ForeignKey(ElectricityPrice)
    consumption = models.DecimalField(max_digits=18, decimal_places=3,
                                      null=True, blank=True, default=None)
    cost = models.DecimalField(max_digits=18, decimal_places=3, null=True,
                               blank=True, default=None)

I think the code I need is something along the lines of the following:

result = ElectricityReading.objects\
        .filter(device__grid__building_id=1) \
        .annotate(num_readings=Count('id'))\
        .annotate(total_consumption=Sum('consumption'))\
        .annotate(total_cost=Sum('cost'))\
        .aggregate(total=Count('datetime__month'))

Right now I'm doing this with this raw SQL:

SELECT
        (EXTRACT(YEAR FROM datetime)) AS reading_date_year,
        (EXTRACT(MONTH FROM datetime)) AS reading_date_month,
        (EXTRACT(DAY FROM datetime)) AS reading_date_day,
        (EXTRACT(HOUR FROM datetime)) AS reading_date_hour,
        SUM(consumption) as total,
        COUNT(id) as num_readings,
        SUM(cost) as total_cost,
        price_id
FROM electricity_reading
WHERE device_id IN (1, 2, 3)
        AND datetime >= '2015-10-01'
        AND datetime <= '2015-10-10'
GROUP BY reading_date_year, reading_date_month, reading_date_day,reading_date_hour, price_id

The part I can't seem to replicate with Django's ORM is the GROUP BY clause at the end, which is what I was expecting to be able to achieve using the aggregate(total=Count('datetime__month')) but instead I get the following error:

FieldError: Cannot resolve keyword 'datetime' into field. Choices are: consumption, cost, created, datetime, device, device_id, id, inserted_by, inserted_by_id, manual, modified, pf, price, price_id, varh, vph1, vph2, vph3, wh_imp, num_readings, total_consumption, total_cost

I would love that someone would tell me I am missing something, and if that's the case, please do! :)

Otherwise, I believe it would be beneficial to add this.

Change History (17)

comment:1 by Raúl Pedro Santos, 9 years ago

Description: modified (diff)

comment:2 by Tim Graham, 9 years ago

It would be helpful if you could include the simplest set of models so we can try the query.

comment:3 by Tim Graham, 9 years ago

Is this a duplicate of #10302?

comment:4 by Raúl Pedro Santos, 9 years ago

Sorry, I should have included the model. I'll edit the original post and add it.

As for the possible duplicate, I did see that post but it didn't look like the same thing (correct me if I'm wrong).

comment:5 by Raúl Pedro Santos, 9 years ago

Description: modified (diff)

comment:6 by Josh Smeaton, 9 years ago

Keywords: QuerySet.extra removed
Triage Stage: UnreviewedAccepted
Version: 1.7master

It's not a duplicate, no, but they are related. #10302 wants transform/lookup support in values() whereas this ticket is asking for support in aggregates/expressions.

1.9 converts transforms into func expressions, so we'll be able to do something like (simplifying the model here..):

from django.db.models.lookups import MonthTransform as Month
result = ElectricityReading.objects.aggregate(total=Count(Month('datetime')))

Which isn't quite as nice as Count('datetime__month'). It should be possible to convert the latter into the former internally though. I would imagine this would be handled internally within F(). Detect if we're trying to access a transform, extract the transform, wrap the original field, and continue as normal. This example (datetime part extraction) is probably the canonical usecase for transform support in aggregates.

If transforms can be supported with underscore syntax within F() objects, then that should solve #10302 as well. There are probably a few more tickets that could be closed with this implementation.

comment:7 by Josh Smeaton, 9 years ago

Cc: josh.smeaton@… added

comment:8 by Markus Holtermann, 9 years ago

Cc: info+coding@… added

comment:9 by Mariusz Felisiak, 5 years ago

Summary: Allow using datetime lookups in QuerySets aggregate callsAllow using lookups in aggregates.

comment:10 by Mariusz Felisiak, 5 years ago

#31469 was closed as a duplicate.

The simplest example for me is Sum('field__abs').

comment:11 by Ian Foote, 4 years ago

Owner: changed from nobody to Ian Foote
Status: newassigned

comment:12 by Ian Foote, 4 years ago

Has patch: set

comment:13 by Mariusz Felisiak, 4 years ago

Needs documentation: set
Needs tests: set

comment:14 by Ian Foote, 4 years ago

Needs documentation: unset
Needs tests: unset

comment:15 by Mariusz Felisiak, 4 years ago

Summary: Allow using lookups in aggregates.Allow using transforms in aggregates.

comment:16 by Mariusz Felisiak, 4 years ago

Triage Stage: AcceptedReady for checkin

comment:17 by Mariusz Felisiak <felisiak.mariusz@…>, 4 years ago

Resolution: fixed
Status: assignedclosed

In 8b040e3c:

Fixed #25534, Fixed #31639 -- Added support for transform references in expressions.

Thanks Mariusz Felisiak and Simon Charette for reviews.

Note: See TracTickets for help on using tickets.
Back to Top