Move python loop to ORM query and fix FieldError in ORM query using Conditional Expressions

[Aditya Saraf]

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?