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
2018. június 20., szerda 16:23:40 UTC+2 időpontban Simon Charette a
következőt írta:
>
> 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+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/3f7d1024-7ec3-496a-8442-7a5ef08f08ec%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
