Django ORM: filter by multiple properties of related object

Question

I have the following objects:

class Image:
  user models.ForeignKey(User)
  corrupted = models.BooleanField()
  final = models.BooleanField()

class ImageRevision:
  image = models.ForeignKey(Image, related_name="revisions")
  corrupted = models.BooleanField()
  final = models.BooleanField()

In English:

1. images can have many revisions
2. images might become corrupted, as well as revisions
3. only one object will be final, i.e. either the image or one of its revisions (an image without revision counts as final)

I would like to get all images from a user that are corrupted.

An image counts as corrupted if any of the following is true:

• the image itself is corrupted and final
• the revision marked as final is corrupted

In the following cases, the image does not count as corrupted:

• the image is final and is not corrupted
• the image has a final revision that is not corrupted

Can this be queried using Django ORM?

Answer 1

You can obtain these images with:

from django.db.models import Q

Image.objects.filter(
    Q(final=True, corrupted=True) |
    Q(revisions__final=True, revisions__corrupted=True)
)

Note that this will also return images that are final and not corrupted, but have a final revision that is corrupt. Based on your comment, this can never happen.

If I understand the logic behind a deleted image/revision correctly, you can filter with:

from django.db.models import Q

Image.objects.filter(
    Q(final=True, corrupted=True, deleted=False) |
    Q(revisions__final=True, revisions__corrupted=True, revisions__deleted=False)
)

This will still include Images who are deleted, but with a non-deleted final revision that is corrupt. We can exclude deleted images completely with:

from django.db.models import Q

Image.objects.filter(
    Q(final=True, corrupted=True) |
    Q(revisions__final=True, revisions__corrupted=True, revisions__deleted=False),
    deleted=False
)