Effectively remove lost m2m / tags objects in Django

Question

I have two models - a photo and a tag - that are linked through ManyToManyField.

class Photo(models.Model): tags = models.ManyToManyField(Tag) class Tag(models.Model): lang = models.CharField(max_length=2) name_es = models.CharField(max_length=40) name_en = models.CharField(max_length=40) 

From time to time we get orphaned tags that no photos link to. Is there an effective way to remove these tags? I know about this answer: Django: delete records of an orphan version of M2M?

And our solution looks like this:

 for tag in Tag.objects.all(): if not tag.photo_set.select_related(): tag.delete() 

However, as the database grows, the execution time of this script becomes extremely difficult: -P Is there an effective way to get a list of all tag identifiers from a tag table, and then a list of all tag identifiers from a many-to-many table to create an intersection list?

Answer 1

Try a subquery with a staging table

 qs = Tag.objects.exclude(pk__in=Book.tags.through.objects.values('tag')) # then you could qs.delete() # or if you need to trigger signal per item for x in qs: x.delete() 

Answer 2

We needed to further improve the performance of this task, so I changed okm's solution a bit:

  all_tag_pks = Tag.objects.values_list('pk', flat=True) used_tag_pks = Photo.tags.through.objects.values_list('tag', flat=True) Tag.objects.filter(pk__in=list(set(all_tag_pks) - set(used_tag_pks))).delete() 

Thus, the database query becomes much smaller and faster.