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/ff88f0f2-0081-4cba-9cc0-7f2e74e65ec8%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
