Hi,
I have the following models defined:
from django.db import models
class IssuedCard(models.Model):
plan_type = models.CharField(max_length=255) # This is actually
a ForeignKey
apn = models.CharField(max_length=255) # This is actually
a Choice
# ... There are other fields not relevant right now
class Usage(models.Model):
class Meta:
unique_together = (
("card", "usage_date"),
)
card = models.ForeignKey(IssuedCard)
usage_date = models.DateField()
upload_bytes = models.BigIntegerField()
download_bytes = models.BigIntegerField()
# ... There are other fields not relevant right now
objects = UsageManager()
class UsageManager(models.Manager):
def
count_of_cards_used_above_x_mb_overall_grouped_by_plan_type_and_apn(self,
x_mb):
qs = self.values("card").annotate(
total_bytes=models.Sum("download_bytes") +
models.Sum("upload_bytes")).order_by().filter(
total_bytes__gt=x_mb*1024*1024).values(
"card__plan_type", "card__apn")
result = create_dict_for_plan_type_and_apn()
for item in qs:
plan_type = item["card__plan_type"]
apn = item["card__apn"]
result[plan_type][apn] += 1
return result
def create_dict_for_plan_type_and_apn(value=0):
plan_types = IssuedCard.objects.all().values_list("plan_type",
flat=True).distinct()
apns = IssuedCard.objects.all().values_list("apn",
flat=True).distinct()
result = dict()
for plan_type in plan_types:
result[plan_type] = dict()
for apn in apns:
result[plan_type][apn] = value
return result
Two things:
-----------
**First**, you might have noticed that the `UsageManager` method doesn't
utilize database to the fullest. It has a bit of python code which loops
through
the queryset to find the count of cards grouped by `plan_type` and `apn`. A
single
card can have multiple `Usage` records for different dates. I have tried
extending the `qs` as defined in the method as:
qs.annotate(models.Count("card", distinct=True))
But, this doesn't give me the count of distinct cards. In fact, it gives the
count of distinct Usage records. I have tried a couple of variations, but
haven't been able to get the correct result through queryset. (Note: we
need not
worry about the fact that queryset would only annotate the combination of
`card__apn` and `card__plan_type` which are actually available in the
database; and
not all the combinations between them).
-------------------------------------------------------------------------------
**Second**, I run
`Usage.objects.count_of_cards_used_above_x_mb_overall_grouped_by_plan_type_and_apn(x_mb)`
a couple of times for different values of `x_mb` and wish to create another
manager method which helps me run a single query for all the different
values
of `x_mb`. For this, I have tried creating the following query using
[Conditional
Expressions](https://docs.djangoproject.com/en/1.9/ref/models/conditional-expressions/):
Usage.objects.values("card").annotate(
total_bytes=models.Sum("download_bytes") +
models.Sum("upload_bytes")).order_by().values(
"card__plan_type", "card__apn").annotate(
more_than_10gb=models.Sum(
models.Case(
models.When(total_bytes__gt=10*1024*1024*1024, then=1),
default=0,
output_field=models.IntegerField()
)
),
more_than_5gb=models.Sum(
models.Case(
models.When(total_bytes__gt=5*1024*1024*1024, then=1),
default=0,
output_field=models.IntegerField()
)
)
# And similar annotations for different data values ....
)
but this gives me an error, saying:
FieldError: Cannot compute Sum('<Case: CASE WHEN <Q: (AND:
('total_bytes__gt', 10737418240))> THEN Value(1), ELSE Value(0)>'): '<Case:
CASE WHEN <Q: (AND: ('total_bytes__gt', 10737418240))> THEN Value(1), ELSE
Value(0)>' is an aggregate
I don't know how to solve this.
-------------------------------------------------------------------------------
Could someone help me and provide some directions to solve them?
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-users+unsubscr...@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-users/0f4087ea-a302-42bf-9e4c-77f365ee2f31%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
