Django many-to-many: Best way to get elements in one related query set but exclude elements in other related query sets?

Consider these related models:

class A(models.Model):
    name = models.CharField(max_length=250, unique=True)
    bs = models.ManyToManyField(B)

class B(models.Model):
    pass

In a view, I need to query to get all Bs related to a given A while excluding all Bs related with a set of other As. I need the grey area:

My current, grossly inefficient approach is as follows:

bs_for_a1 = A.objects.get(name=a1).bs.all()

for previous_A in previous_As:
    previous_bs = A.objects.get(name=previous_A).bs.all()
    bs_for_a1 = bs_for_a1.difference(previous_bs)

In my application, previous_As could have nearly 1,000 elements, which makes this approach extremely expensive. Is there a better way to do this? I have complete control to overhaul the models/schema. I would like any given query, including selecting related models and associated sorting, to finish in under 1 second.

I’m thinking a better approach might involve a raw SQL query directly on the transition table. I’m using Django 3.0 with sqlite. Production will use Postgres.

Answer

It seems I was making this harder than necessary. This seems to work for the given illustration:

previous_As = (a2.name, a3.name, a4.name)
b1_minus_previous = B.objects.exclude(a__name__in=previous_As).filter(a__name=a1)

Advertisement

Answer