Aggregates are giving the wrong numbers for .annotate()? What am I doing wrong?

Hi all,

I've been working in django for a while now, but am still wrapping my
head around the more complex queryset features.

I have something like this:

class Foo:
name = CharField()
bars = ForeignKey(Bar)
widgets = ForeignKey(Widget)

I can do this:

Foo.objects.extra(select={
'num_bars': 'SELECT COUNT(*) FROM app_bar WHERE app_bar.foo_id =
app_foo.id',
'num_widgets': 'SELECT COUNT(*) FROM app_widget WHERE
app_widget.foo_id = app_foo.id',
})

That gives me what I want... a list of all foo's with counts of how
many bars & widgets it's linked to.

Name Num Bars Num Widgets
First 1 4
Second 2 3

However, I wanted to use annotations. So I do this:

Foo.objects.annotate(num_bars=Count(bar), num_widgets=Count(widget))

But I get increased counts:

Name Num Bars Num Widgets
First 19 19
Second 5 5

When I spit out the str(queryset.query) var, I get SQL like this:

SELECT
app_foo.name,
COUNT(app_bar.id) AS num_bars,
COUNT(app_widget.id) AS num_widgets
FROM app_foo
LEFT OUTER JOIN app_bar ON ...
LEFT OUTER JOIN app_widget ON ...
WHERE ...
GROUP BY app_food.id

This seems to fail because each LEFT OUTER JOIN gives multiple rows
for each match in the resulting table. Then when COUNT() goes to
work, it will count each of the duplicate rows as another entry. Then
all the COUNT() vars end up being a) larger, and b) the same as each
other, since they're all counting the end-result rows.

I've tried .distinct(), but it does not fix the problem. Am I
misunderstaning .annotate()? Is this a bug? Thanks!

--
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+unsubscribe@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/django-users?hl=en.