How to ask the Django manytomany manager to match several relations at once?

Question

I've got this model:

class Movie(models.Model):
    # I use taggit for tag management
    tags = taggit.managers.TaggableManager()

class Person(models.Model):
    # manytomany with a intermediary model
    movies = models.ManyToManyField(Movie, through='Activity')

class Activity(models.Model):
    movie = models.ForeignKey(Movie)
    person = models.ForeignKey(Person)
    name = models.CharField(max_length=30, default='actor')

And I'd like to match a movie that has the same actors as another one. Not one actor in common, but all the actors in common.

So I don't want this:

# actors is a shortcut property
one_actor_in_common = Movie.object.filter(activities__name='actor', 
                                           team_members__in=self.movie.actors)

I want something that would make "Matrix I" match "Matrix II" because they share 'Keanu Reeves' and 'Laurence Fishburne', but not match "Speed" because they share 'Keanu Reeves' but not 'Laurence Fishburne'.

Answer 1

The many to manytomany manager cannot match several relations at once. Down at the database level it all comes down to selecting and grouping.

So naturally speaking the only question the database is capable to answer is this: List acting activites that involve these persons, group them by movie and show only those movies having the same number of those acting activities as persons.

Translated to ORM speak it looks like this:

actors = Person.objects.filter(name__in=('Keanu Reaves', 'Laurence Fishburne'))

qs = Movie.objects.filter(activity__name='actor',
                          activity__person__in=actors)
qs = qs.annotate(common_actors=Count('activity'))
all_actors_in_common = qs.filter(common_actors=actors.count())

The query this produces is actually not bad:

SELECT "cmdb_movie"."id", "cmdb_movie"."title", COUNT("cmdb_activity"."id") AS "common_actors" 
    FROM "cmdb_movie" 
    LEFT OUTER JOIN "cmdb_activity" ON ("cmdb_movie"."id" = "cmdb_activity"."movie_id") 
    WHERE ("cmdb_activity"."person_id" IN (SELECT U0."id" FROM "cmdb_person" U0 WHERE U0."name" IN ('Keanu Reaves', 'Laurence Fishburne')) 
           AND "cmdb_activity"."name" = 'actor' )
    GROUP BY "cmdb_movie"."id", "cmdb_movie"."title", "cmdb_movie"."id", "cmdb_movie"."title" 
    HAVING COUNT("cmdb_activity"."id") = 2

I also have a small application that I used to test this, but I don't know wether someone needs it nor where to host it.