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?

Does this answer your question? [How to get all objects referenced as ForeignKey from given field in a module in django](https://stackoverflow.com/questions/34184046/how-to-get-all-objects-referenced-as-foreignkey-from-given-field-in-a-module-in) — Abdul Aziz Barkat, Sep 11 '22 at 16:40
I think it's in the right direction, but still not quite. I'm not sure how to filter this correctly in the right kind of F() function — Yellow, Sep 11 '22 at 16:46
You don't need an `F` expression (You cannot annotate a queryset or an entire object anyway)... As per the duplicate target: `for parent in Parent.objects.all():` and then `for child in parent.child_set.all(): print(child)` This makes multiple queries but you can use `prefetch_related` to make it more efficient. — Abdul Aziz Barkat, Sep 11 '22 at 16:51
I still don't think this answers my question. I know I can do it in plain Python, but I *need* is as an annotation, for what I want to do next with it. — Yellow, Sep 11 '22 at 16:59
I just told you above, you **can't** annotate a queryset / entire object. And you can access the related objects quite easily using the related manager, if your concerns are filtering `brothers = parent.child_set.filter(status="MALE")` is completely valid code. — Abdul Aziz Barkat, Sep 11 '22 at 17:02

score 1 · Accepted Answer · edited Sep 12 '22 at 09:07

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')
)

Find annotated list of siblings in Django

1 Answers1