>From the lack of other folks jumping in saying "do it this way", I'm going to guess that this is a hard problem. I am not a database heavyweight. (IANADbH) I especially don't know about limitations of MySQL.
It seems that you need to calculate a maximum size of Llama objects, but separately for each LlamaHerd, and then you have to select, again for each LlamaHerd, choose a Llama of hte corresponding size (what if there are multiple animals in that herd with that size?) to include in the result, and then attach an order by date of birth to the final result. I don't know how to do that in one SQL statement. I do know from experience that it's easy, by choosing a naive approach, to have such an aggregate size of join tables that the database starts to swap. So lets hope that a DbG (Database Guru) pipes up with an answer. On Wed, Apr 2, 2014 at 9:31 PM, Justin Holmes <jus...@justinholmes.com>wrote: > I think you misunderstand: I am not actually tracking llama herds. :-) > > This is just example code; I haven't actually tested it in the way that > you suggest. The problem isn't with this code in particular, it's with > this concept in general. This is a question I have had again and again in > the past few years - this code is just a way to articulate it. > > So, in short - no, the code won't work as now written, and it's obvious > why: annotate doesn't assign instances, it assigns results. > > I don't think I need an annotation expert per se; it may well be that > annotate is the wrong way to do this. > > My SQL isn't particularly strong, but if I were writing this in SQL, I > suspect that GROUP_BY is necessary or at least plausible. I know that > Django doesn't directly expose GROUP_BY, but it looks likes > QuerySet.values() uses it; so I wonder if that fits into the picture > somehow? > > > On Wed, Apr 2, 2014 at 12:35 PM, Bill Freeman <ke1g...@gmail.com> wrote: > >> My point was that annotate might work after the other problems were >> fixed. Have you re-tested since? If your annotate still doesn't work then >> perhaps someone with more annotate experience than I will comment. >> >> >> >> On Wed, Apr 2, 2014 at 12:04 PM, Justin Holmes >> <jus...@justinholmes.com>wrote: >> >>> No no, the annotate doesn't work - that's the whole reason for my >>> question. See, the annotate doesn't associate instances with the annotated >>> attribute, it associated results. >>> >>> (BTW, good looking out - I have once again fixed the SO question :-)) >>> >>> >>> On Wed, Apr 2, 2014 at 10:17 AM, Bill Freeman <ke1g...@gmail.com> wrote: >>> >>>> 1. You now have a foreign key to Herd, but you don't have a model by >>>> that name. It's called LlamaHerd. >>>> >>>> 2. I was misinterpreting what you wanted (couldn't get past the lack >>>> of a relationship while reading0. Once the relationship is fixed, your >>>> original annotate might work. >>>> >>>> >>>> On Tue, Apr 1, 2014 at 6:20 PM, Justin Holmes >>>> <jus...@justinholmes.com>wrote: >>>> >>>>> You say "use the reverse relation manager in LlamaHerd to build up >>>>> your annotation," but that answer the question. >>>>> >>>>> How can I sort LlamaHerd objects by the DOB of their largest llama? >>>>> >>>>> >>>>> On Tue, Apr 1, 2014 at 6:13 PM, Justin Holmes <jus...@justinholmes.com >>>>> > wrote: >>>>> >>>>>> Yes, absolutely - my bad. I have updated to SO question. I meant >>>>>> the models to look like: >>>>>> >>>>>> class LlamaHerd(models.Model): >>>>>> shepherd = ForeignKey(User) >>>>>> >>>>>> class Llama(models.Model): >>>>>> size = FloatField() >>>>>> dob = >>>>>> FloatField >>>>>> herd = ForeignKey(Herd, related_name="llamas") >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> On Tue, Apr 1, 2014 at 6:09 PM, Bill Freeman <ke1g...@gmail.com>wrote: >>>>>> >>>>>>> Aren't you missing a ForeignKey relationship to tell which LlamaHerd >>>>>>> a Llama belongs to? >>>>>>> >>>>>>> Then you would use the reverse relation manager in LlamaHerd to >>>>>>> build up your annotation. >>>>>>> >>>>>>> >>>>>>> On Tue, Apr 1, 2014 at 5:52 PM, Justin Holmes < >>>>>>> jus...@justinholmes.com> wrote: >>>>>>> >>>>>>>> I have come across this problem in several Django projects, but >>>>>>>> only just now worked it out in my head enough to make it a cognizable >>>>>>>> question. >>>>>>>> >>>>>>>> I have also posted it on StackOverflow, here: >>>>>>>> http://stackoverflow.com/questions/22797497/using-the-django-orm-to-order-by-a-value-in-a-distinct-related-item >>>>>>>> >>>>>>>> >>>>>>>> Consider this example: >>>>>>>> >>>>>>>> class LlamaHerd(models.Model): >>>>>>>> shepherd = ForeignKey(User) >>>>>>>> >>>>>>>> class Llama(models.Model): >>>>>>>> size = FloatField() >>>>>>>> dob = FloatField >>>>>>>> >>>>>>>> How can I now make a query to get LlamaHerd objects, sorted by the >>>>>>>> date of birth of their largest llama? >>>>>>>> >>>>>>>> It almost feels like I might be able to use annotate: >>>>>>>> >>>>>>>> LlamaHerd.annotate(largest_llama=Max('llama__size')).order_by('largest_llama__dob') >>>>>>>> >>>>>>>> ...but the annotate here creates fields with float values, not with >>>>>>>> the model instances. >>>>>>>> >>>>>>>> >>>>>>>> -- >>>>>>>> Justin Holmes >>>>>>>> Chief Chocobo Breeder, slashRoot >>>>>>>> >>>>>>>> slashRoot: Coffee House and Tech Dojo >>>>>>>> New Paltz, NY 12561 >>>>>>>> 845.633.8330 >>>>>>>> >>>>>>>> -- >>>>>>>> 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 http://groups.google.com/group/django-users. >>>>>>>> To view this discussion on the web visit >>>>>>>> https://groups.google.com/d/msgid/django-users/CAMGywB6gkBvhFs%2BP2PrtDah24VDCko8QK-1UCnSGugz_ts1v8g%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAMGywB6gkBvhFs%2BP2PrtDah24VDCko8QK-1UCnSGugz_ts1v8g%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>>>>> . >>>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>>> >>>>>>> >>>>>>> -- >>>>>>> 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 http://groups.google.com/group/django-users. >>>>>>> To view this discussion on the web visit >>>>>>> https://groups.google.com/d/msgid/django-users/CAB%2BAj0srKNzRs%3DzbsPm%2BobxLNFCcjRccGSVeYykF1PSnJc-ydQ%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAB%2BAj0srKNzRs%3DzbsPm%2BobxLNFCcjRccGSVeYykF1PSnJc-ydQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>>>> . >>>>>>> For more options, visit https://groups.google.com/d/optout. >>>>>>> >>>>>> >>>>>> >>>>>> >>>>>> -- >>>>>> Justin Holmes >>>>>> Chief Chocobo Breeder, slashRoot >>>>>> >>>>>> slashRoot: Coffee House and Tech Dojo >>>>>> New Paltz, NY 12561 >>>>>> 845.633.8330 >>>>>> >>>>> >>>>> >>>>> >>>>> -- >>>>> Justin Holmes >>>>> Chief Chocobo Breeder, slashRoot >>>>> >>>>> slashRoot: Coffee House and Tech Dojo >>>>> New Paltz, NY 12561 >>>>> 845.633.8330 >>>>> >>>>> -- >>>>> 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 http://groups.google.com/group/django-users. >>>>> To view this discussion on the web visit >>>>> https://groups.google.com/d/msgid/django-users/CAMGywB4V3zpbqr7v1kcvOi8%2BoqLPm0nr18dGStaedys494%2BOag%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAMGywB4V3zpbqr7v1kcvOi8%2BoqLPm0nr18dGStaedys494%2BOag%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>>> . >>>>> >>>>> For more options, visit https://groups.google.com/d/optout. >>>>> >>>> >>>> -- >>>> 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 http://groups.google.com/group/django-users. >>>> To view this discussion on the web visit >>>> https://groups.google.com/d/msgid/django-users/CAB%2BAj0vE%2Bc8feae_j8v5_U9UHod1RaphytQQASXD1Dwq4RBGDw%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAB%2BAj0vE%2Bc8feae_j8v5_U9UHod1RaphytQQASXD1Dwq4RBGDw%40mail.gmail.com?utm_medium=email&utm_source=footer> >>>> . >>>> >>>> For more options, visit https://groups.google.com/d/optout. >>>> >>> >>> >>> >>> -- >>> Justin Holmes >>> Chief Chocobo Breeder, slashRoot >>> >>> slashRoot: Coffee House and Tech Dojo >>> New Paltz, NY 12561 >>> 845.633.8330 >>> >>> -- >>> 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 http://groups.google.com/group/django-users. >>> To view this discussion on the web visit >>> https://groups.google.com/d/msgid/django-users/CAMGywB4mV1%3DFBeECvwOYP3o-ksm_KNN4s6NX7TpCbVCv70h%2BzQ%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAMGywB4mV1%3DFBeECvwOYP3o-ksm_KNN4s6NX7TpCbVCv70h%2BzQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >>> . >>> >>> For more options, visit https://groups.google.com/d/optout. >>> >> >> -- >> 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 http://groups.google.com/group/django-users. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/django-users/CAB%2BAj0tjx%3Dvf3R5T-COATEXF%2BchmUNXiP1vN0HWiMzC7Ws-aYQ%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAB%2BAj0tjx%3Dvf3R5T-COATEXF%2BchmUNXiP1vN0HWiMzC7Ws-aYQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> >> For more options, visit https://groups.google.com/d/optout. >> > > > > -- > Justin Holmes > Chief Chocobo Breeder, slashRoot > > slashRoot: Coffee House and Tech Dojo > New Paltz, NY 12561 > 845.633.8330 > > -- > 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 http://groups.google.com/group/django-users. > To view this discussion on the web visit > https://groups.google.com/d/msgid/django-users/CAMGywB7P1r4UsjZOn%2B8_CdKCY-x2OPsTHySjQpFT0ciUuZGUZw%40mail.gmail.com<https://groups.google.com/d/msgid/django-users/CAMGywB7P1r4UsjZOn%2B8_CdKCY-x2OPsTHySjQpFT0ciUuZGUZw%40mail.gmail.com?utm_medium=email&utm_source=footer> > . > > For more options, visit https://groups.google.com/d/optout. > -- 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 http://groups.google.com/group/django-users. To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/CAB%2BAj0tRCNr3kDJ07ECZdqHEeA8Opha2H38cGEOeg0OKzy_v5A%40mail.gmail.com. For more options, visit https://groups.google.com/d/optout.
