id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
14511	exclude() generates wrong query for ManyToManyField with a 'through' relationship	George Sakkis	nobody	"Consider the following sample models:
{{{
from django.db import models

class Person(models.Model):
    name = models.CharField(max_length=128)

class Company(models.Model):
    name = models.CharField(max_length=128)
    employees = models.ManyToManyField(Person, related_name='employers', through='Employment')

class Employment(models.Model):
    employer = models.ForeignKey(Company)
    employee = models.ForeignKey(Person)
    title = models.CharField(max_length=128)
}}}

The generated query for finding all the companies that 'alex' has worked for as an engineer or developer looks good:
{{{
>>> alex = Person.objects.get(name='alex')
>>> alex.employers.filter(employment__title__in=('Engineer', 'Developer')).distinct()
SELECT DISTINCT `lib_company`.`id`,
                `lib_company`.`name`
FROM `lib_company`
INNER JOIN `lib_employment` ON (`lib_company`.`id` = `lib_employment`.`employer_id`)
WHERE (`lib_employment`.`employee_id` = 1
       AND `lib_employment`.`title` IN (Engineer, Developer))
}}}

However the inverse -- finding all the companies that alex has worked for but '''not''' as an engineer or developer -- is wrong:
{{{
>>> alex.employers.exclude(employment__title__in=('Engineer', 'Developer')).distinct()
SELECT DISTINCT `lib_company`.`id`,
                `lib_company`.`name`
FROM `lib_company`
INNER JOIN `lib_employment` ON (`lib_company`.`id` = `lib_employment`.`employer_id`)
WHERE (`lib_employment`.`employee_id` = 1
       AND NOT (`lib_company`.`id` IN
                  (SELECT U1.`employer_id`
                   FROM `lib_employment` U1
                   WHERE U1.`title` IN (Engineer, Developer))))
}}}

So basically this returns all the companies that alex has worked for that don't have any engineer or developer employee, not just alex. Here's a test that reproduces the problem:

{{{
import unittest

class ExcludeJoinTest(unittest.TestCase):
    def test(self):
        alex = Person.objects.get_or_create(name='Alex')[0]
        jane = Person.objects.get_or_create(name='Jane')[0]

        oracle = Company.objects.get_or_create(name='Oracle')[0]
        google = Company.objects.get_or_create(name='Google')[0]
        microsoft = Company.objects.get_or_create(name='Microsoft')[0]
        intel = Company.objects.get_or_create(name='Intel')[0]

        def employ(employer, employee, title):
            Employment.objects.get_or_create(employee=employee, employer=employer, title=title)

        employ(oracle, alex, 'Engineer')
        employ(oracle, alex, 'Developer')
        employ(google, alex, 'Engineer')
        employ(google, alex, 'Manager')
        employ(microsoft, alex, 'Manager')
        employ(intel, alex, 'Manager')

        employ(microsoft, jane, 'Developer')
        employ(intel, jane, 'Manager')

        alex_tech_employers = list(alex.employers.filter(employment__title__in=('Engineer', 'Developer')).distinct())
        self.assertEqual(alex_tech_employers, [oracle, google])

        alex_nontech_employers = list(alex.employers.exclude(employment__title__in=('Engineer', 'Developer')).distinct())
        self.assertEqual(alex_nontech_employers, [microsoft, intel])

======================================================================
FAIL: test (lib.models.ExcludeJoinTest)
----------------------------------------------------------------------
Traceback (most recent call last):
  File ""/home/george/lib/models.py"", line 53, in test
    self.assertEqual(alex_nontech_employers, [microsoft, intel])
AssertionError: [<Company: Intel>] != [<Company: Microsoft>, <Company: Intel>]
}}}"	Bug	closed	Database layer (models, ORM)	1.2	Normal	fixed			Accepted	0	0	0	0	0	0