CODEWITHSUNDEEP
django
Альберт Александров
Альберт Александров

Reputation: 821

Django - Group by and do ArrayAgg

I have a model:

class Deviation(models.Model):
    deviation_number = models.SmallIntegerField()
    day_of_calculation = models.SmallIntegerField()

And I'd like to group records of the model by day_of_calculation and get list of deviation_numbers:

deviations = Deviation.objects \
    .values('day_of_calculation') \
    .annotate(deviation_numbers=ArrayAgg('deviation_number'))

But Django incorrectly creates sql:

SELECT "deviation"."day_of_calculation", ARRAY_AGG("deviation"."deviation_number" ) AS "deviation_numbers" FROM "deviation" GROUP BY "deviation"."day_of_calculation", "deviation"."deviation_number"

Grouping by deviation_number should not happen.

What do I do wrong?

Upvotes: 3

Views: 5784

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477170

You should add a .order_by(…) clause [Django-doc] to force a GROUP BY on the fields listed in the .order_by(…), so:

Deviation.objects.values('day_of_calculation').annotate(
    deviation_numbers=ArrayAgg('deviation_number')
).order_by('day_of_calculation')

Upvotes: 5

Related Questions