Re: Annotate an object with the value of a filtered, related object's attribute
> 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 Le dimanche 21 mai 2017 11:08:37 UTC-4, Yo'av Moshe a écrit : > > 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. > > > > On Sunday, 21 May 2017 17:53:25 UTC+3, Simon Charette wrote: >> >> 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+unsubscr...@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/9e0d71fd-9c90-4eed-926c-7f28d25ee939%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
Re: Annotate an object with the value of a filtered, related object's attribute
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. On Sunday, 21 May 2017 17:53:25 UTC+3, Simon Charette wrote: > > 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+unsubscr...@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/b3a4222d-622b-4399-a704-8d9961d23c36%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.
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+unsubscr...@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.
Annotate an object with the value of a filtered, related object's attribute
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+unsubscr...@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/23ef9a3e-36f6-4c70-a3ea-3ff102db2e3f%40googlegroups.com. For more options, visit https://groups.google.com/d/optout.