Django query to select parent with nonzero children

Question

I have model with a Foreign Key to itself like this:

class Concept(models.Model):
    name = models.CharField(max_length=200)
    category = models.ForeignKey('self')

But I can't figure out how I can select all concepts that have nonzero children value. Is this possible with django QuerySet API or I must write custom SQL?

Answer 1

If I understand it correctly, each Concept may have another Concept as parent, and this is set into the category field.
In other words, a Concept with at least a child will be referenced at least once in the category field.

Generally speaking, this is not really easy to get in Django; however if you do not have too many categories, you can think for a query of the like of SELECT * FROM CONCEPTS WHERE CONCEPTS.ID IN (SELECT CATEGORY FROM CONCEPTS); - and this is something you can map easily with Django:

Concept.objects.filter(pk__in=Concept.objects.all().values('category'))

Note that, as stated on Django documentation, this query may have performance issues on certain databases; therefore you should instead put it as a list:

Concept.objects.filter(id__in=list(Concept.objects.all().values('category')))

But please be aware that this could hit some database limitation -- for instance, Oracle allows up to 1000 elements in such lists.

Answer 2

How about something like this:

concepts = Concept.objects.exclude(category=None)