I posted this in StackOverflow, and haven't gotten an answer there yet. I'm hoping someone here can answer my question. I don't know if this represents a django bug, or a lack of understanding on my part.
Link: http://stackoverflow.com/questions/7276147/interactions-among-values-annotate-and-aggregation-in-django-querysets Copy of text: I have these two models: class Leaf(models.Model): pass class Root(models.Model): leafs = models.ManyToManyField(Leaf) species = models.CharField(max_length=20) age = models.FloatField() objects = CustomQuerySetManager() class QuerySet(query.QuerySet): def small(self): return self.annotate(leaf_count=models.Count('leafs')).\ filter(leaf_count__lt=6) def oldest_each_species(self): oldest_each_species = set() for d in self.values('species').annotate(models.Max('age')): some_oldest = self.filter( species=d['species'], age=d['age__max'] )[0] oldest_each_species.add(some_oldest) return oldest_each_species And this test harness: class AggTest(test.TestCase): def set_up(self): pass def test_surprise(self): for i in range(0,2): root = models.Root(species='oak', age=i) root.save() for j in range(0,4): leaf = models.Leaf() leaf.save() root.leafs.add(leaf) print "All Roots" for root in models.Root.objects.small(): print root.id, root.species, root.leafs.count() print "All oldest" for matron in models.Root.objects.oldest_each_species(): print matron.id, matron.age print "All small oldests DOESN'T WORK" for matron in models.Root.objects.small().oldest_each_species(): print matron.id, matron.age print "All small oldests again" for matron in models.Root.objects.\ filter(id__in=models.Root.objects.small()).\ oldest_each_species(): print matron.id, matron.age which prints: All Roots 1 oak 4 2 oak 4 All oldest 2 1.0 All small oldests DOESN'T WORK All small oldests again 2 1.0 I was surprised by the result, but the interactions between values(), annotate(), and aggregate() often surprise me. Unwrapping my query, I have Root.objects.annotate(leaf_count=models.Count('leafs')). filter(leaf_count__lt=6).values('species'). annotate(models.Max('age')) which turns into the SQL SELECT `autest_root`.`species`, MAX(`autest_root`.`age`) AS `age__max` FROM `autest_root` LEFT OUTER JOIN `autest_root_leafs` ON (`autest_root`.`id` = `autest_root_leafs`.`root_id`) GROUP BY `autest_root`.`species`, `autest_root`.`species` HAVING COUNT(`autest_root_leafs`.`leaf_id`) < 6 ORDER BY NULL As shown in the test [at "All small oldests again"], I have a work- around, adding a nested django query on the ids, so I'm not looking for a work-around, but better work-arounds are welcome. I would say I don't understand the interactions well enough to even know if this is a bug in django. If it is, I'll continue to use a work- around. If not a bug, I would like a better understanding of these interactions, and how to manage situations like this. My best guess would be to have small() return the nested query, to encapsulate the fact that it is using annotations to do its job. So: * Is this a bug in django? * Is there some documentation which explains what I'm seeing? * What is the "best practice" for dealing with this kind of problem? -- You received this message because you are subscribed to the Google Groups "Django users" group. To post to this group, send email to django-users@googlegroups.com. To unsubscribe from this group, send email to django-users+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/django-users?hl=en.
