django query not grouping properly

Question

    class Category(BaseEntity):
        """ to categorise the expense """
        name = models.CharField(
            max_length=80,
            validators=[
                RegexValidator(
                    regex='^[a-zA-Z\s]*$',
                     message='name should contain only alphabets',
                    code='invalid_name'
                ),
            ]
        )
        description = models.CharField(
            max_length=250,
            validators=[
                RegexValidator(
                    regex='^[a-zA-Z\s]*$',
                     message='name should contain only alphabets',
                    code='invalid_name'
                ),
            ],
            null = True,
            blank = True
        )

        parent_category = models.ForeignKey(
                'self',
                related_name = 'child_categories',
                on_delete = models.CASCADE,
                null = True,
                blank = True
        )



class Expense(BaseEntity):
    """covers all the expenses"""

    SEGMENT_CHOICES=(
        ('RENTALS', 'Rentals'),
        ('MIXING', 'Mixing'),
        ('ALBUMS', 'Albums'),
        ('SALES', 'Sales'),
    )

    amount = models.IntegerField(
            default=500,
            validators=[
                MinValueValidator(
                    0,
                    message = 'Amount should be greater than 0'
                ),

                MaxValueValidator(
                    100000,
                    message = 'Amount should be less than 100000'
                ),
            ]
        )
    category = models.ForeignKey(
            'accounting.Category',
            related_name='expenses',
            on_delete=models.CASCADE,
            null = True,
            blank = True
    )

    date = models.DateField(
            verbose_name='spending date'
        )

    description = models.CharField(
        max_length=250,
        validators=[
            RegexValidator(
                regex='^[a-zA-Z\s]*$',
                 message='name should contain only alphabets',
                code='invalid_name'
            ),
        ],
        null = True,
        blank = True
    )
    event = models.ForeignKey(
            'booking.Event',
            related_name='event_expenses',
            blank = True,
            null =True,
            on_delete=models.CASCADE
        )
    business_segment= models.CharField(
        max_length =15,
        choices = SEGMENT_CHOICES,
        default = 'RENTALS',
    )

query:

expense_category = Expense.objects.values('category__name').annotate(total=Sum('amount'))

outputlooks like as follows:

 <QuerySet [{'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 2000}, {'category__name': 'Diesel', 'total': 3000}, {'category__name': 'web live', 'total': 1600}, {'category__name': 'Diesel', 'total': 2200}, {'category__name': 'Diesel', 'total': 1000}, {'category__name': 'Diesel', 'total': 1000}, {'category__name': 'Diesel', 'total': 500}, {'category__name': 'Diesel', 'total': 1600}, {'category__name': 'Diesel', 'total': 2500}, {'category__name': 'Bills', 'total': 200}, {'category__name': 'Diesel', 'total': 5600}]>

even though Diesel has many entries it is not grouped properly. Am I missing anything in the query. I don't like to do list processing for this, I want it by the query method. List logic will just comlicate the thing and waste of processing power

Answer 1

Annotate Category objects (with an extra attribute)

First of all, I think it makes more sense to query from the Category object, then perhaps we do not even need to perform a proper group by, like:

Category.objects.annotate(total=Sum('expenses__amount'))

This will result in a QuerySet where every Category has an extra attribute .total. This might be better, since then you have access to all attributes of Category, and it will behave like a Category.

Querying for a QuerySet of dictionaries

If you only want a QuerySet of dictionaries, you need to add an .order_by(..) attribute at the end (yes, I know that sounds weird), like:

qs = Category.objects.values(
    'name'
).annotate(
    total=Sum('expenses__amount')
).order_by('name')

This will then result in a:

<QuerySet [
    { 'name': 'bar', 'total': 14.0 }
    { 'name': 'foo', 'total': 25.0 }
]>

Note however that if two categories have the same name, these will add up, which might not be ideal.