Annotate an object with the value of a filtered, related object's attribute

[Yo'av Moshe]

Hey Djangoists!
I can't get my head around this and I'm not sure if it's even possible.

Let's say I have an "Adult" object and a "Child" object. Every Child belongs to an Adult, and has a gender which is either "M" or "F", and also a "dob" field with their date of birth. I want to get a list of all adults annotated with the dob of their oldest son, and the dob of their oldest daughter.

How am I to do this?

I tried something like this:

Adult.objects.annotate(
   oldest_son_dob=Case(
       When(children__gender="M", then=F('children__dob')),
       default=None,
       output_field=DateField(),
   )
)

# ... same for daughter

but I'm not sure where to tell Django that I only want it to pick the oldest child, and so right now it duplicates the adult object for every child it has.

Does Django support this kind of query?

I'm using PosgresSQL FWIW.

Thank you so much

Yo'av

[Simon Charette]

Hello Yo'av,

You'll want to use subqueries for this[0].

from django.db.models import OuterRef, Subquery

children = Child.objects.filter(adult=OuterRef('pk')).order_by('dob').values('dob')

Adult.objects.annotate(

    oldest_son_dob=Subquery(children.filter(gender='M')[:1]),

    oldest_daughter_dob=Subquery(children.filter(gender='F')[:1]),

)

Note that I haven't tried the above code myself so it might required adjustments.

Cheers,

Simon

[0] https://docs.djangoproject.com/en/1.11/ref/models/expressions/#subquery-expressions

[Yo'av Moshe]

Thanks, never heard of Subqueries before! It's time to upgrade to Django 1.11 I guess.

Performance-wise, do you know if it's any different than running over my Adult objects and for each one of them running a separate query looking for their oldest son & daughter? Or is it the same and it just looks better?

Thanks again.

[Simon Charette]

> Performance-wise, do you know if it's any different than running over my Adult objects and for each one of them running a separate query looking for their oldest son & daughter? Or is it the same and it just looks better?

It should perform better as everything will be performed in a single query
by the database.

The equivalent SQL is along

SELECT adult.*,
    (SELECT dob FROM child WHERE adult_id = adult.id AND gender = 'M' ORDER BY dob LIMIT 1) AS oldest_son_dob,
    (SELECT dob FROM child WHERE adult_id = adult.id AND gender = 'F'  ORDER BY dob LIMIT 1) AS oldest_daughter_dob
FROM adult;

Cheers,
Simon