Find annotated list of siblings in Django

Question

I have a simple database with two models that define a parent-child relationship. In it, a child can have two possible gender, "Male" or "Female".

class Parent(models.Model):
    id = models.UUIDField(primary_key=True, editable=False, unique=True, )
    name =  models.CharField(max_length=64)


MALE = "MALE"
FEMALE = "FEMALE"

class Child(models.Model):
    id = models.UUIDField(primary_key=True, editable=False, unique=True, )
    name =  models.CharField(max_length=64)

    parent = models.ForeignKey(
        Parent,
        null=True,
        on_delete=models.SET_NULL)

    GENDER = [
        (MALE, 'Male'),
        (FEMALE, 'Female'),
    ]
    status = models.CharField(
        max_length=8,
        choices=GENDER
    )

For the purposes of this question, a parent will only ever have zero or one male children, and zero or one female children. (Though this is not enforced in the database model definition.)

What I would like to achieve is an annoted query, that returns all Parent objects, annoted with their male child and female child. I can't quite figure out how to produce this: I can get a list of all parents, all male and all female children, but I don't know how to put them together so that the right children are with the right parent.

This is far as I get:

annotated_parent_set = Parent.objects.get_queryset()
brothers = Child.objects.filter(gender=MALE)
sisters = Child.objects.filter(gender=FEMALE)
annotated_parent_set = annotated_parent_set.annotate(
    brother=F(???))
)
annotated_parent_set = annotated_parent_set.annotate(
     sister=F(???))
)

How can I now merge these guys to get the annotation I want?

Answer 1

You don't need to annotate this, you can .prefetch_related(…) ^[Django-doc] this with:

annotated_parent_set = Parent.objects.prefetch_related('child_set')

or if you want to use .brothers and .sisters, then you can work with two Prefetch objects ^[Django-doc]:

from django.db.models import Prefetch


annotated_parent_set = Parent.objects.prefetch_related(
    Prefetch('child_set', Child.objects.filter(status=MALE), to_attr='brothers'),
    Prefetch('child_set', Child.objects.filter(status=FEMALE), to_attr='sisters')
)