Django filter exact m2m objects

Question

Let's say I have a team model, and a team has members.

So

class Team(models.Model):
    team_member = models.ManyToManyField('Employee')

class Employee(models.Model):
    ....

Lets say I have a list of employee ids like team_members = [1001, 1003, 1004] and I want to find the Team, that is made up of exactly those three members.

I don't want the team that has [1001, 1003, 1004, 1005] or the team that has [1001, 1003].

Only team [1001, 1003, 1004].

This is what I'm doing now:

teams = Team.objects.all()
for t in teams:
    if set([x.id for x in t.team_member.all()]) == set(team_members):
        team = t
if not team:
    team = Team.objects.create()
    team.team_member = team_members

But it seems a bit ham-handed. Is there a cleaner way, with fewer nested loops?

Answer 1

The short answer

No, I don't know of a much simpler way in terms of code appearance.

However there are some things you could do to make your code a little more graceful and potentially a lot faster. Plus it is possible to do the work in the database, albeit quite inefficiently for large team sizes.

The DB option listed below is pretty much as ham-handed as the for loop you provided, but could be more efficient depending on your data set, DB, etc.

Longer answer: ways to be less 'ham-handed'

There are a couple of places I'd clean up the style here.

Plus, in my experience with Django, loops like the one you built do tend to become pretty expensive on large data sets. If you end up loading, say, 10,000 teams into memory, having the ORM convert them to Team objects, and then iterating over them, you'll probably see some significant slowdown.

Two things to try for speed & grace:

1. Use Team.values_list('team_members') for your in-python filter loop, which skips the step where Django organizes all of the SQL data into Model objects. I've found this to save lots of time instantiating objects (sometimes around an order of magnitude).
2. straighten out your set() calls. Currently you're re-converting team_members to a set() on every iteration, plus you're turning t.team_member implicitly into TeamMember objects (as they're fetched from the DB), then into a list of ids and then into a set. For the first item, just make a team_members_set = set(team_members) up front and reuse it. For the second item, you can do set(t.team_member.values_list('id', flat=True)) which will skip the heaviest ORM step of instantiating TeamMembers (which could be as bad as O(n^2) in your example depending on the data set and Django's caching).
3. use Team.objects.all().iterator() to not load the Teams all into memory at once. This will help if you're running into memory issues.

But with any performance optimization, of course test your perf with real or real-ish data to be sure you're not making things worse!

Longer answer: the DB option

After trying all manner of Q() manipulation and other approaches listed in the answers here, to no avail, I found this answer by @Todor.

Basically you need to do repeated filter()s, one for each team_member. On top of that you use a Count filter to make sure that you don't end up choosing a Team with a superset of the desired members.

desired_members = [1001, 1003, 1004]
initial_queryset = Team.objects.annotate(cnt=models.Count('team_members')).filter(cnt=len(desired_members))
matching_teams = reduce( # Can of course use a for loop if you prefer that to reduce()
    lambda queryset, member: queryset.filter(team_members=member),
    desired_members,
    initial_queryset
)

Note that the resulting query will likely have perf issues for large teams, since it will do one JOIN for every one of your desired_members. It'd be nice to avoid that but I don't know of another way to do this all in the database without changing your data structure. I'd love to learn a better way, and if you end up doing some perf testing I'd be curious to find what you learn!