CODEWITHSUNDEEP
pythondjangopostgresqldjango-modelsdjango-orm
Filipe Spindola
Filipe Spindola

Reputation: 1955

Django ORM count group by issue

I have the following model:

class Referral(models.Model):
    referrer = models.ForeignKey(Profile, related_name='referral_made')
    recipient = models.ForeignKey(
        Profile, related_name='referral_received')
    created_at = models.DateTimeField(auto_now=True)

I need to group by the number of referrals for each recipient

This query works:

Referral.objects.values('recipient_id').annotate(total_referral=Count('recipient_id')).order_by('-total_referral')

and the output is

[{'total_referral': 5, 'recipient_id': 929}, {'total_referral': 1, 'recipient_id': 143}]

The problem is that I need query objects for later use

If I remove the ".values('recipient_id')" I get separated records and not grouped.

Referral.objects.annotate(total_referral=Count('recipient_id')).order_by('-total_referral')

[Referral: Referral object, Referral: Referral object, Referral: Referral object, Referral: Referral object, Referral: Referral object, Referral: Referral object]

I have made a lot of search and tested some answers but can't manage to get the results as Query objects

Any thoughts?

Upvotes: 0

Views: 367

Answers (1)

Chitharanjan Das
Chitharanjan Das

Reputation: 1333

It should be possible to achieve what you're trying to do using reverse relationships. Since you've used the related_name on the recipient key, let's suppose you start with the following queryset:

profiles = Profile.objects.prefetch_related('referral_received').all()

when you loop over each object in this queryset, the referrals received by that profile will be available (without any additional queries, since we've used prefetch_related above), like so:

groups = []
for each_profile in profiles:
    group_qs = each_profile.referral_received.all()
    groups.append({
        "recipient_id": each_profile.id,
        "total_referral": len(group_qs), 
        "queryset": group_qs,
    })

Note that if you don't use prefetch_related, then a separate query will be fired inside each loop iteration, and that will end up costing you a fair bit of I/O wait time, depending on the number of profiles.

Even though this approach eschews database-layer grouping in favor of grouping in python, the good news is that you're still able to do this using just a single database query.

Upvotes: 1

Related Questions