Search code examples
pythonmysqldjangodjango-modelsdjango-orm

django ORM - Exclude on M2M with F-function not working

I have the following models:

class Activity(models.Model):
   user = models.ForeignKey(User, related_name='activities')
   project = models.ForeignKey(Project, related_name='activities')


class Project(models.Model):
   assignees = models.ManyToManyField(User, related_name='projects')

Now I want to query for all the activities which belog to a user which is NOT in the projects assignees.

My query:

Activity.objects.exclude(project__assignees=F('user'))

Problem is, I always get this error:

django.db.utils.OperationalError: (1054, "Unknown column 'U2.id' in 'on clause'")

Im working on django 2.2.11 and MySQL.

I found a couple of old django bugs but they are supposed to be fixed since ages.

Any ideas how I can solve my problem? My activity table is huge and I need an efficient way. And I'd be happy to not use raw queries.

Solution

  • It looks like this is a bug, since in the generated query, the U2 table is not even defined. You might want to file a ticket at Django's ticketing system.

    You can however solve the problem by annotating and then filtering:

    from django.db.models import Exists, OuterRef

Activity.objects.annotate(
    valid=~Exists(Project.objects.filter(
        activities=OuterRef('pk'),
        assignees=OuterRef('user')
    ))
).filter(valid=True)

    or as of , you can filter on the Exists directly:

    from django.db.models import Exists, OuterRef

Activity.objects.filter(
    ~Exists(Project.objects.filter(
        activities=OuterRef('pk'),
        assignees=OuterRef('user')
    ))
)