CODEWITHSUNDEEP
djangohierarchical-datadjango-queryset
dragoon
dragoon

Reputation: 5744

Django query to select parent with nonzero children

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?

Upvotes: 5

Views: 3508

Answers (3)

rob
rob

Reputation: 37684

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.

Upvotes: 4

Ignacio Vazquez-Abrams
Ignacio Vazquez-Abrams

Reputation: 799280

The way you have it written there will require a value for category. Once you have fixed that (with null=True in the field constructor), use this:

Concept.objects.filter(category__isnull=False)

Upvotes: 3

Luiz C.
Luiz C.

Reputation: 776

How about something like this:

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

Upvotes: 3

Related Questions