Code

Opened 5 years ago

Closed 5 years ago

#9759 closed (duplicate)

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

Reported by: gregplaysguitar Owned by: mtredinnick
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: UI/UX:

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.

Attachments (0)

Change History (2)

comment:1 Changed 5 years ago by mtredinnick

  • Needs documentation unset
  • Needs tests unset
  • Owner changed from nobody to mtredinnick
  • Patch needs improvement unset
  • Status changed from new to assigned

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 Changed 5 years ago by mtredinnick

  • Resolution set to duplicate
  • Status changed from assigned to closed

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

Add Comment

Modify Ticket

Change Properties
<Author field>
Action
as closed
as The resolution will be set. Next status will be 'closed'
The resolution will be deleted. Next status will be 'new'
Author


E-mail address and user name can be saved in the Preferences.

 
Note: See TracTickets for help on using tickets.