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
9759	exclude() does not return the opposite of filter() for one to many relationships	Greg Brown	Malcolm Tredinnick	"Consider the following models (in an app called ""news""):

{{{
class Region(models.Model):
    name = models.CharField(max_length=255)

class Career(AuditedBaseModel):
    name = models.CharField(max_length=255)
    region = models.ForeignKey(Region)
}}}


I'm trying to select all regions that have at least one career, using:

{{{
Region.objects.exclude(career=None)
}}}

but I'm getting no results. The opposite - retrieving all regions with no career - works fine:

{{{
Region.objects.filter(career=None)
}}}

The filter() call above uses the following sql:

{{{
SELECT ""news_region"".""id"", ""news_region"".""name""
FROM ""news_region""
LEFT OUTER JOIN ""news_career"" ON (""news_region"".""id"" = ""news_career"".""region_id"")
WHERE ""news_career"".""id"" IS NULL;
}}}

and the exclude() uses:

{{{
SELECT ""news_region"".""id"", ""news_region"".""name""
FROM ""news_region""
WHERE NOT (
    ""news_region"".""id"" IN (
        SELECT U1.""region_id"" 
        FROM ""news_region"" U0 
        LEFT OUTER JOIN ""news_career"" U1 ON (U0.""id"" = U1.""region_id"") 
        WHERE U1.""id"" IS NULL
    )
);
}}}


which doesn't work. I guess there's reasons for it to be done this way, but if exclude actually just wrapped the filter() clause in a NOT(), ie:

{{{
SELECT ""news_region"".""id"", ""news_region"".""name""
FROM ""news_region""
LEFT OUTER JOIN ""news_career"" ON (""news_region"".""id"" = ""news_career"".""region_id"")
WHERE NOT(""news_career"".""id"" IS NULL);
}}}

it would work fine. Also, the presence of a ""not equal"" operator would solve my problem.
"		closed	Database layer (models, ORM)	1.0		duplicate	"""not equal"", exclude, filter"		Unreviewed	0	0	0	0	0	0