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

Answer 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?)

Answer 2

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

Person.objects.filter(company = None)