annotate with query set for distinct values

[SlafS]

Hi there!
I have a question. If i have a model with some fields (let's say all
CharFields named aaa,bbb,ccc etc. and some calculated properties named
xxx and zzz) how can I obtain something similar to

"SELECT aaa, count(aaa) FROM my_model_table GROUP BY aaa;"
i.e. a list of - lazy loaded - objects with distinct aaa values and an
extra column which indicates how many entries of a specific aaa are in
the table.

I would like to acheive that with QuerySet and annotate so that I can
get a list of objects to use their other properties (xxx or zzz) e.g.
in my template

Regards

[Dan Harris]

Here is an example:

# In models.py
class TestModel(models.Model):
name = models.CharField()


# Get a listing of unique names and their counts
# In some view
from django.db.models import Count

TestModel.objects.values("name").annotate(Count("name"))

This will return a list of the form:

[{'name__count': 2, 'name': u'aaaa'}, {'name__count': 1, 'name':
u'bbbbbbb'}]

Hope this helps!

Dan Harris
dih...@gmail.com

[SlafS]

Thanks.

I've already tried that but this isn't quite what i'm looking for. As
u wrote this returns a list, but I would like to obtain my calculated
property when I'm iterating over this. Like so (let's say the result
is in res variable) :

{% for r in res %}
{{r.xxx}} - {{r.name__count}}
{% endfor %}

I'm starting to think that there's no such nice way to acheive that :/

On 10 Cze, 23:06, Dan Harris <dih0...@gmail.com> wrote:
> Here is an example:
>
> # In models.py
> class TestModel(models.Model):
>    name = models.CharField()
>
> # Get a listing of unique names and their counts
> # In some view
> from django.db.models import Count
>
> TestModel.objects.values("name").annotate(Count("name"))
>
> This will return a list of the form:
>
> [{'name__count': 2, 'name': u'aaaa'}, {'name__count': 1, 'name':
> u'bbbbbbb'}]
>
> Hope this helps!
>
> Dan Harris

> dih0...@gmail.com

[Dan Harris]

The problem is the GROUP BY you are looking to get. The problem with
doing a group by you don't get the whole object which is what the
Django queryset is trying to return:

res = queryset.annotate(Count("name"))

This will give you a result set that you want and you can iterate over
like:

{% for r in res %}

{{ r.name }} - {{ r.name__count}}
{% endfor %}

however there may be duplicated {{ r.name }}

Dan Harris
dih...@gmail.com

[SlafS]

Yes, I'm aware of the background of the problem but i was wondering if
I can somehow "workaround" it.

Thanks for Your help

On 10 Cze, 23:34, Dan Harris <dih0...@gmail.com> wrote:
> The problem is the GROUP BY you are looking to get. The problem with
> doing a group by you don't get the whole object which is what the
> Django queryset is trying to return:
>
> res = queryset.annotate(Count("name"))
>
> This will give you a result set that you want and you can iterate over
> like:
>
> {% for r in res %}
>    {{ r.name }} - {{ r.name__count}}
> {% endfor %}
>
> however there may be duplicated {{ r.name }}
>
> Dan Harris

[sebastien piquemal]

I think you didn't really look well anotate, because it looks like it
is what you qre looking for ! Or is there something I misunderstood ?

http://docs.djangoproject.com/en/dev/ref/models/querysets/#annotate-args-kwargs

"Annotates each object in the QuerySet with the provided list of
aggregate values (averages, sums, etc) that have been computed over
the objects that are related to the objects in the QuerySet. Each
argument to annotate() is an annotation that will be added to each
object in the QuerySet that is returned."

[Scott Gould]

It's not really a matter of "working around" it. Your .xxx method/
property is an attribute of the object. What you evidently want from
the database is *not* a list of those objects, but rather a summary
representation of them. Trying to apply your .xxx is meaningless as
you don't have a discrete object to apply it to. If it *is* supposed
to be meaningful in that context, then it's probably the sort of thing
that belongs in a manager.

If you can tell us a bit more about what .xxx actually is, or is
supposed to do, then we might be able to come up with an alternate
solution.

Regards
Scott