Django ORM dublicate objects in queryset after order_by by foreignkey field
I have encountered some unexpected sorting behaviour with objects. As soon as I sort by the related model field, I get duplicates. A short description of the model fields
class GoogleCreativeSpend(models.Model): creative = models.ForeignKey( 'GoogleCreative', on_delete=models.CASCADE, ) spend = models.DecimalField() class GoogleCreative(CreamCreative): .....
Create some objects:
>>> creative = GoogleCreative.objects.get(name='gs_video1031v1') >>> spend = GoogleCreativeSpend(creative=creative, spend=100,) >>> spend.save() >>> spend = GoogleCreativeSpend(creative=creative, spend=1100,) >>> spend.save() >>> spend = GoogleCreativeSpend(creative=creative, spend=1,) >>> spend.save()
queryset = GoogleCreative.objects.all() queryset.order_by('googlecreativespend__spend') for i in queryset: if i.name == 'gs_video1031v1': print(i.name) | gs_video1031v1 | gs_video1031v1 | gs_video1031v1
I.e. by creating 3 googlespend objects I get 3 duplicates for googlecreative after sorting.
According to How to sort queryset based on foreign key with no duplicates
But it doesn't work
How to fix it ?
Finde the solution using Avg
queryset = queryset.annotate(spend=Avg('googlecreativespend__spend')) queryset.order_by('spend')