How to calculate count in Django through two m2m levels?

Question

I have 3 models like:

class Customer(models.Model):
    hobby_groups=models.ManyToManyField(
        'HobbyGroup',
        blank=True,
        related_name='group_customers',
    )


class HobbyGroup(models.Model):
    hobbies = models.ManyToManyField(
        'Hobby',
        blank=True,
        related_name='hobby_groups',
    )

class Hobby(models.Model):
    title = models.CharField(max_length=255, default='football')

And I need to calculate count of hobbies for each customer.

qs = Customer.objects.annotate(
    hobbies_count=Count('hobby_groups__hobbies', distinct=True)
)

With distinct it works fine, but very slow.

I've tried to use Subquery.

hobbies = Hobby.objects.filter(hobby_groups__group_customers=OuterRef('pk')).values('pk')
hobbies_count = hobbies.annotate(count=Count('*')).values('count')
qs = Customer.objects.annotate(
    hobbies_count=Subquery(hobbies_count)
)

But it returns exception 'More than one row returned by a subquery used as an expression'

Is there any way to calculate it faster or fix second solution? Because I did similar for backward related models and it worked fine and fast.

Thanks in advance for help.

score 0 · Answer 1 · answered Mar 06 '20 at 00:53

0

You can add a custom property in your Customer model to do the counting.

class Customer(models.Model):
    hobby_groups=models.ManyToManyField(
        'HobbyGroup',
        blank=True,
        related_name='group_customers',
    )

    @property
    def count_hobbies(self):
        return Hobby.objects.filter(
            hobby_groups__group_customers=self
        ).distinct().count()

Then, if you have an Customer instance, john for example, you can call john.count_hobbies to get this customer's number of hobbies.

answered Mar 06 '20 at 00:53

Jun Zhou

1,077
1
6
19

thx for the answer. I was thinking about adding property, but I think that it won't be faster, because I need to have this field in admin change-list for many instances, so instead of one request, there will be separate request for each instance. But for update/detail page it's fine. – marcustas Mar 06 '20 at 09:28
AFAIK, the property field can be called by each Customer instance. Am I missing something here? – Jun Zhou Mar 06 '20 at 09:34
1

I've checked your solution. It makes more queries to db, but works faster. Thanks. – marcustas Mar 06 '20 at 10:57
Nice, glad to hear that. – Jun Zhou Mar 06 '20 at 11:04

score 0 · Answer 2 · answered Mar 25 '20 at 18:04

I think your subquery is almost right, just one of your values needs to be different to get Django to generate the correct group by:

hobbies = Hobby.objects.filter(
    hobby_groups__group_customers=OuterRef('pk')
    ).values('hobby_groups__group_customers')

hobbies_count = hobbies.annotate(count=Count('*')).values('count')

qs = Customer.objects.annotate(
    hobbies_count=Subquery(hobbies_count)
)

Alternatively, you could use the package django-sql-utils and do it much more simply:

from sql_util.utils import SubqueryCount

qs = Customer.objects.annotate(
    hobbies_count=SubqueryCount('hobby_groups__hobbies')
)

Thx for the answer, need to check it. – marcustas Mar 31 '20 at 10:58 — marcustas, Mar 31 '20 at 10:58

How to calculate count in Django through two m2m levels?

2 Answers2