QuerySet filter with multiple ManyToMany field lookups does not behave as expected

Question

I am trying to write a simple Django filter which doesn't seem to work as expected. I have a User model and a Vacation model and I want to write a query to get all users that are not on vacation.

class User(models.Model):
    vacation = models.ManyToManyField('Vacation')


class Vacation(models.Model):
    start = models.DateTimeField()
    end = models.DateTimeField()

The obvious solution I tried was:

now = timezone.now()

User.objects.exclude(vacation__start__lte=now, vacation__end__gt=now)

This doesn't work. This queryset will also exclude any User objects that have a future vacation (start time and end time after now). I think this is a result of the combination of the exclude and the many to many lookup but I can't find any documentation on why this behaves this way and there doesn't seem to be a way to get to the simple result using simple queryset chain. The only way I've got this to work is by querying User objects on vacation and then running another queryset excluding users with those IDs. Ex:

users_on_vacation = User.objects.filter(vacation__end__gte=now, vacation__start__lte=now).values_list('pk', flat=True)
users_not_on_vacation = User.objects.exclude(pk__in=users_on_vacation)

Is there a simpler way to write this filter?

Answer 1

Have a look at https://docs.djangoproject.com/en/dev/topics/db/queries/#spanning-multi-valued-relationships (the second note in particular). You'll see exclude behaves slightly differently to filter when applying criteria to 'each' in a m2m set rather than 'any'.

The way round provided on that page, fitted to your case, would look something like:

User.objects.exclude(
    vacation__in = Vacation.objects.filter(
         start__lte=now, 
         end__gt=now
    ),
)