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

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

Le dimanche 21 mai 2017 10:41:44 UTC-4, Yo'av Moshe a écrit :

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

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users+unsubscribe@googlegroups.com.
To post to this group, send email to django-users@googlegroups.com.
Visit this group at https://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/837f340c-6020-4932-a971-c4dee0e2bbf9%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.