[Django] #25534: Allow using datetime lookups in QuerySets aggregate calls
Django
----------------------------------------------+----------------------------
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
Component: Database layer (models, ORM) | Version: 1.7
Severity: Normal | Keywords: QuerySet.extra
Triage Stage: Unreviewed | Has patch: 0
Easy pickings: 0 | UI/UX: 0
----------------------------------------------+----------------------------
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.
I think the code I need is something along the lines of the following:
{{{#!python
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:
{{{#!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 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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25534>
Django <https://code.djangoproject.com/>
The Web framework for perfectionists with deadlines.
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: QuerySet.extra | Triage Stage:
| Unreviewed
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by borfast):
* needs_better_patch: => 0
* needs_tests: => 0
* needs_docs: => 0
Old description:
New description:
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:1>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
It would be helpful if you could include the simplest set of models so we
can try the query.
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:2>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by timgraham):
Is this a duplicate of #10302?
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:3>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Comment (by borfast):
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).
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:4>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
| Unreviewed
Easy pickings: 0 | UI/UX: 0
Description changed by borfast:
Old description:
> 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.
New description:
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}}}):
{{{#!python
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)
}}}
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.
--
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:5>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* keywords: QuerySet.extra =>
* version: 1.7 => master
* stage: Unreviewed => Accepted
Comment:
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.
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:6>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by jarshwah):
* cc: josh.smeaton@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:7>
Django
Reporter: borfast | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by MarkusH):
* cc: info+coding@… (added)
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:8>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: nobody
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:9>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: new
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Comment (by felixxm):
#31469 was closed as a duplicate.
The simplest example for me is `Sum('field__abs')`.
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:10>
Django
-------------------------------------+-------------------------------------
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 0 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ian Foote):
* owner: nobody => Ian Foote
* status: new => assigned
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:11>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 0 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Ian Foote):
* has_patch: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:12>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Needs tests: 1 | Patch needs improvement: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* needs_docs: 0 => 1
* needs_tests: 0 => 1
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:13>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Easy pickings: 0 | UI/UX: 0
Changes (by Ian Foote):
* needs_docs: 1 => 0
* needs_tests: 1 => 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:14>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
Keywords: | Triage Stage: Accepted
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:15>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Type: New feature | Status: assigned
Component: Database layer | Version: master
(models, ORM) |
Severity: Normal | Resolution:
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak):
* stage: Accepted => Ready for checkin
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:16>
Django
-------------------------------------+-------------------------------------
Reporter: Raúl Pedro Santos | Owner: Ian Foote
Component: Database layer | Version: master
(models, ORM) |
Keywords: | Triage Stage: Ready for
| checkin
Has patch: 1 | Needs documentation: 0
Easy pickings: 0 | UI/UX: 0
Changes (by Mariusz Felisiak <felisiak.mariusz@…>):
* status: assigned => closed
* resolution: => fixed
Comment:
In [changeset:"8b040e3cbbb2e81420e777afc3ca48a1c8f4dd5a" 8b040e3c]:
{{{
#!CommitTicketReference repository=""
revision="8b040e3cbbb2e81420e777afc3ca48a1c8f4dd5a"
Fixed #25534, Fixed #31639 -- Added support for transform references in
expressions.
Thanks Mariusz Felisiak and Simon Charette for reviews.
}}}
--
Ticket URL: <https://code.djangoproject.com/ticket/25534#comment:17>