Aggregation issue

[Gallusz Abaligeti]

Hi Folks!

I have a little problem in connection with aggregation through Django's ORM. The sketch of my model is very simple with some custom field types (but those types are irrelevant in the problem):

# Fields types

class MoneyField(models.DecimalField):
    def __init__(self, *args, **kwargs):
        kwargs['null'] = True
        kwargs['blank'] = True
        kwargs['max_digits'] = 15
        kwargs['decimal_places'] = 2
        super().__init__(*args, **kwargs)

class RevenueField(MoneyField):
    def __init__(self, *args, **kwargs):
        kwargs['validators'] = [MinValueValidator(0)]
        kwargs['null'] = True
        kwargs['blank'] = True
        super().__init__(*args, **kwargs)

class WeakTextField(models.CharField):
    def __init__(self, *args, **kwargs):
        kwargs['max_length'] = 200
        kwargs['null'] = True
        kwargs['blank'] = True
        super().__init__(*args, **kwargs)

class NameField(WeakTextField):
    def __init__(self, *args, **kwargs):
        kwargs['unique'] = True
        super().__init__(*args, **kwargs)

class YearField(models.PositiveIntegerField):
    def __init__(self, *args, **kwargs):
        kwargs['validators'] = [
            MinValueValidator(1900),
            MaxValueValidator(2100),
        ]
        kwargs['null'] = True
        kwargs['blank'] = True
        super().__init__(*args, **kwargs)

class WeakForeignKey(models.ForeignKey):
    def __init__(self, *args, **kwargs):
        kwargs['null'] = True
        kwargs['blank'] = True
        kwargs['on_delete'] = models.SET_NULL
        super().__init__(*args, **kwargs)  

# Model entities

class Company(models.Model):
    registration_number = NameField(_('Registration number')) # custom field, defined above
    name = NameField(_('Name'))
    ...
    ..
    .

class Financial(models.Model):
    financial_year = YearField(_('Financial year'))
    company = WeakForeignKey(to='Company', verbose_name=_('Company'))
    revenue = RevenueField(_('Revenue'))
    ...
    ..
    .

    class Meta:
        unique_together = (('financial_year', 'company'),)

My goal is to compose a query with QuerySet like this:

SELECT financial_year, SUM(revenue)
FROM financial
GROUP BY financial_year

As far as I could understand the ORM it should be done like this:

qs = Financial.objects.values('financial_year').annotate(Sum('revenue'))

however if i print out the SQL query it has an extra field after the Group By statement:

SELECT
    "data_manager_financial"."financial_year",  
    CAST(SUM("data_manager_financial"."revenue") AS NUMERIC) AS "revenue__sum"
FROM "data_manager_financial"  
LEFT OUTER JOIN "data_manager_company"  
ON ("data_manager_financial"."company_id" = "data_manager_company"."id")  
GROUP BY  
    "data_manager_financial"."financial_year",  
    "data_manager_company"."name"  
ORDER BY "data_manager_company"."name"  
ASC, "data_manager_financial"."financial_year" ASC

I'm afraid this problem is related with the unique constraint of the Financial entity. Of course the problem can be solved through raw SQL or through a separate entity for the financial year field but i dont like them. Do you have any ideas?

Thanks a lot!

Gallusz

[Simon Charette]

Hello Gallusz,

This is probably caused by the `Financial.Meta.ordering` that you didn't include in your snippet.

I assume it's currently defined as ('name', 'financial_year')?

In order to get rid of it you'll want to clear it up using an empty call to order_by():

Financial.objects.order_by().values('financial_year').annotate(Sum('revenue'))

FWIW there's currently an issue to stop automatically considering Meta.ordering when performing

GROUP'ing through values().[0][1]

Best,

Simon

[0] https://code.djangoproject.com/ticket/14357

[1] https://github.com/django/django/pull/10005

[Gallusz Abaligeti]

Hi!

Yes exactly, the ordering meta was

ordering = ('company', 'financial_year',)

and addig order_by('financial_year') to the QuerySet solved the problem (https://stackoverflow.com/questions/50944536/django-aggregation-issue)

Thanks,

Gallusz