Opened 16 years ago

Closed 16 years ago

#9759 closed (duplicate)

exclude() does not return the opposite of filter() for one to many relationships

Reported by: Greg Brown Owned by: Malcolm Tredinnick
Component: Database layer (models, ORM) Version: 1.0
Severity: Keywords: "not equal", exclude, filter
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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.

Change History (2)

comment:1 by Malcolm Tredinnick, 16 years ago

Owner: changed from nobody to Malcolm Tredinnick
Status: newassigned

You never want anything involving the word "equality" when working with NULLs, since they don't compare equal to anything. Fortunately, there's already the __isnull lookup type, so filtering on career__isnull=False will do what you want (it will generate the latter SQL). Spotting that the exclude() call you wrote is a special case that can be transformed into the __isnull equivalent is not something Django will do -- it's quite complex to spot such transformations efficiently and always make the correct one.

I cannot immediately spot the problem with the exclude() call, however. Since I'm working on this area, I'll add this to my test cases.

comment:2 by Malcolm Tredinnick, 16 years ago

Resolution: duplicate
Status: assignedclosed

Closing as a dupe of #8921 after creating a test case.

Note: See TracTickets for help on using tickets.
Back to Top