Find roots of many-to-many category tree in Django

Question

I have a Django model like:

class Category(models.Model):
    status=models.CharField(max_length=16)
    machineName=models.CharField(max_length=50)
    readableName=models.CharField(max_length=100)
    description=models.CharField(max_length=1024)
    parents=models.ManyToManyField('self')

Where each category may exist in many parents. Some categories have no parents, they are the "root" categories. In plain SQL I could find them by:

SELECT "readableName"
FROM foo_category AS c
LEFT JOIN foo_category_parents AS cp ON (c.id=cp.from_category_id)
WHERE cp.to_category_id IS NULL;

And indeed, this works well. How do I find the "list of categories with no parents" with a Django-y call? I tried:

# Says "Cannot resolve keyword 'is_null' into field."
Category.objects.filter(parents__is_null=True)
# Says "Join on field 'id' not permitted."
Category.objects.filter(parents__pk_null=True)

But as noted, neither work.

Answer 1

Django's many-to-many fields normally operate symmetrically (see this entry in the Django docs). When you do a ManyToMany to self, this means that a reverse ManyToMany entry will be created, so in fact every category with a parent will be the parent of the parent (if that makes sense).

In other words:

a = Category.objects.create(name='a')
b = Category.objects.create(name='b')
b.parents.add(a)

print b.parents.all() # produces [a]
print a.parents.all() # produces [b], which is why your filter is failing

To get around this, there's a special option:

class Category(models.Model):
    # ... as above ...
    parents=models.ManyToManyField('self', symmetrical=False)

Now you can get the parent categories with:

 Category.objects.filter(parents=None)