Count common ManyToMany members in queryset

Question

Assuming I have such models:

class Bar(models.Model):
    pass # some simple Model goes here

class Foo(models.Model):
    bars = models.ManyToManyField(Bar)

And some variable main_object = Foo() with bars filled, how can I make a Queryset so that it's annotated with the number of common bars elements betweeen each entity and main_object?

Example:

There are three Bar records, with primary keys 1, 2 and 3. main_object has pk=2 as member set in bars.

Foo has two records: main_object and another with pk=1 set in bars. In this case, I want an annotation that has the value of 0, since said record has no common Bar foreign keys with main_object.

I can imagine something similar to Foo.objects.filter(bars__in=<some_values_here>) but instead of simply checking presence, actually counting it like from django.db.models.Count.

Is it even solvable via Querysets or I should resort to manual counting through loops?

In practical use, such way of querying can be useful in similarity ranking, but it seems non-trivial for me.

I am not sure, but `Foo.objects.filter(bars__in=main_objects.bars.all())` may be the thing you want — sudden_appearance, Feb 28 '22 at 10:30
@sudden_appearance you may be right, I was considering it but couldn't find a source if such annotation would be more "inclusive" than should be. — Zoltán Schmidt, Feb 28 '22 at 10:31

score 1 · Accepted Answer · answered Feb 28 '22 at 11:05

You can count with:

from django.db.models import Count, Q

Foo.objects.annotate(
    common_count=Count(
        'bars',
        filter=Q(bars__foo=main_object)
    )
)

or if you only want to retrieve objects that have at least one Bar in common:

Foo.objects.filter(
    bars__foo=main_object
).distinct()

Count common ManyToMany members in queryset

1 Answers1