CODEWITHSUNDEEP
django
Nie Selam
Nie Selam

Reputation: 1451

Delete unused parent models

I have these two models:

Parent(models.Model):
   name=models.CharField(max_length=10)

Child(models.Model):
   parent=models.ForeignKey(Parent)
   name=models.CharField(max_length=10)

I am looking for an efficient way to remove parent's with no children. Currently, I am doing it with RAW SQL:

 from django.db import connection
 SQL="DELETE FROM app_parent WHERE app_parent.id NOT IN (SELECT id FROM app_child"

Which is running two queries and I still want to use Django ORM. With ORM, as well, I am first getting list of children first then check if parent id exists there or not.

Is there a faster way of doing that?

Upvotes: 0

Views: 286

Answers (1)

willeM_ Van Onsem
willeM_ Van Onsem

Reputation: 477749

With a LEFT OUTER JOIN

You can detect Parents without a child, by .filter(..)ing with the related_name, and check if that relation __isnull.

So we can get a list of Parents with no child by writing:

Parent.objects.filter(child__isnull=True).distinct()

The .distinct() is important here, since otherwise a parent can occur multiple times. Behind the curtains, Django will perform a query that looks like:

SELECT DISTINCT `parent`.*
FROM `parent`
LEFT OUTER JOIN `child` ON (`parent`.`id` = `child`.`parent_id`)
WHERE (`child`.`id` IS NULL)

We can then .delete() those Parents:

Parent.objects.filter(child__isnull=True).distinct().delete()

With an EXISTS query

We can also work with an exists query:

from django.db.models import Exists, OuterRef

Parent.objects.annotate(
    has_children=Exists(
        Child.objects.filter(parent=OuterRef('pk').values('id'))
    )
).where(has_children=False).delete()

So here we annotate every Parent with the fact whether or not a Child exists that has as parent, the pk of the Parent. We then .filter(..) on the fact that this annotation should be False.

This will result in a query like:

SELECT `parent`.*
FROM `parent`
WHERE NOT EXISTS (
    SELECT `child`.`id`
    FROM `child`
    WHERE `child`.`parent_id` = `parent`.`id`
) = True

Protected parent objects (through to other relations)

Note however that if the Parent is still referenced through other ForeignKeys, this might fail. If for example there is not only a child, but an Uncle relation, that refers to a Parent, then the .delete() could fail, since there are still Uncles that refer to one or more Parents without a child.

Upvotes: 2

Related Questions