Re: Aggregation issue

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

Le mercredi 20 juin 2018 07:38:19 UTC-4, Gallusz Abaligeti a écrit :

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

--
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+unsubscribe@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/7f545416-3425-4cc1-8476-12337d4e3419%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.