Django ForeignKey with null=True, inner join, and left outer join

Question

Let's say I have two Django models Person and Company as follows: -

class Company(models.Model):
    name = models.CharField()

class Person(models.Model):
    last_name = models.CharField(blank=True)
    first_name = models.CharField()
    company = models.ForeignKey(Company, null=True, blank=True)

A Person may or may not belong to a Company.

I am using MySQL. I want all Persons that do not belong to any Company, that is, Persons where company is null.

If I do Person.objects.filter(company__isnull=True) I get an SQL which is essentially: -

SELECT * FROM PersonTable LEFT OUTER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

How do I go about achieving the following SQL: -

SELECT * FROM PersonTable INNER JOIN AgencyTable ON (PersonTable.company_id = AgencyTable.id) WHERE AgencyTable.id IS NULL

From what I gather from reading up the Django Users mailing list, this used to be the behavior before QuerySet Refactor.

EDIT -- Now I see the blasphemy of my question!

What I want to say is I simply want to do

SELECT * FROM PersonTable WHERE PersonTable.company_id IS NULL

Well, if this is not making sense to you, this is actually a 'base' query that gets INNER JOINed with other queries, and this leads to weird, repetitive results. — chefsmart, Feb 17 '10 at 06:14

score 15 · Answer 1 · answered Apr 23 '11 at 02:16

Well, this question is old, and soon the patch will be in Django. But for the brief meantime, the answer is in http://code.djangoproject.com/ticket/10790:

Workaround: Instead of

Person.objects.filter(company=None)

use

Person.objects.exclude(company__isnull=False)

Crast · Answer 2 · 2010-02-17T17:33:48.963

1

It should be as simple as:

Person.objects.filter(company_id__isnull=True)

Note the use of company_id which is the default integer field created by the ForeignKey

Edit

Sorry, I haven't actively used django since 0.9.5. Either I'm thinking of pre-1.0 behavior, or I'm muddling up sqlalchemy and Django ORM. In either case, as the comments stated, the above appears to not work.

It looks like the only way to get the query you want in current django is to use the .extra query parameter, which comes with a whole list of caveats.

Person.objects.extra(where=['company_id IS NULL'])

Note that this may not be portable to all DB's, and it may not work combined with filter(), and any number of possible issues. I would recommend not using this throughout your code, and instead moving it to a classmethod on Person like:

 @classmethod
 def list_unaffiliated_people(cls):
    return cls.objects.extra(where=['company_id IS NULL'])

Alternately, just use the proper ORM query syntax and suck up the possible performance hit (have you actually benchmarked the more complicated query to see that it's any slower?)

edited Feb 17 '10 at 17:33

answered Feb 17 '10 at 06:23

Crast

15,996
5
45
53

Tried it out in the shell, it gives me FieldError: Cannot resolve keyword 'company_id' into field. Choices are: company, first_name, last_name – chefsmart Feb 17 '10 at 06:40
Hrm. I thought that it was possible to query on the key like that. I guess it's back to the drawing board on that one. – Crast Feb 17 '10 at 07:01
That's not possible. `company__id__isnull` would be valid, but would generate pretty much the same SQL. – ayaz Feb 17 '10 at 12:09
@ayaz, Crast was referring to company_id (single underscore). I think you are referring to company__id (double underscore) – chefsmart Feb 18 '10 at 02:48
@chefsmart: That's correct. I wanted to point out that the way `company_id` is referenced in a QuerySet method is not only going to not work but also give an error. – ayaz Feb 18 '10 at 07:57

score 0 · Answer 3 · answered Feb 17 '10 at 18:25

0

Django will treat NULL as Python's None object so:

Person.objects.filter(company = None)

answered Feb 17 '10 at 18:25

Zach

18,594
18
59
68

This also gives the same SQL. I guess the way Django is doing it is not that bad after all. I will focus on how I can improve my other lookups and see if I can solve my problem. – chefsmart Feb 18 '10 at 02:56

Django ForeignKey with null=True, inner join, and left outer join

3 Answers3