How to annotate many values in one queryset

[9dev...@gmail.com]

Is it possible to annotate two different values to one QuerySet?

Example queryset:

Item.objects.annotate(score=Count('votes'))

I would like every item in the above QuerySet to contain the following field:

current_user_vote = Vote.objects.filter(user=request.user).filter(item=item)

I tried to use annotate() two times, however it filtered out everything except items current user voted on.

[Collin Anderson]

This one comes up a lot. As far as I can tell, "extra" is the only way to go.

http://stackoverflow.com/questions/2096580/filtering-only-on-annotations-in-django

http://stackoverflow.com/questions/4620385/django-annotation-with-nested-filter

http://www.djangofoo.com/182/filter-annotate-count

http://timmyomahony.com/blog/filtering-annotations-django/

[Tom Evans]

On Mon, Aug 18, 2014 at 1:06 PM, <9dev...@gmail.com> wrote:
> Is it possible to annotate two different values to one QuerySet?
>
> Example queryset:
>
> Item.objects.annotate(score=Count('votes'))

Yes, simply pass it as an additional argument to annotate(). An
example from one of my projects:

shows = TVSeries.objects.all().order_by('name').annotate(
num_episodes=Count('tvepisode'),
num_episodes_with_media=Count('tvepisode__media_id'),
num_aired_episodes=Count('tvepisode__airdate'),
num_seasons=Max('tvepisode__season'))

So it counts the number of episodes, how many have media, how many
have aired, and what the maximum season is for each series.

>
> I would like every item in the above QuerySet to contain the following
> field:
>
> current_user_vote = Vote.objects.filter(user=request.user).filter(item=item)

As an aside, this can probably be written as:

current_user_vote = item.vote_set.filter(user=request.user)

>
> I tried to use annotate() two times, however it filtered out everything
> except items current user voted on.

Just use it once.

Cheers

Tom

[Collin Anderson]

> shows = TVSeries.objects.all().order_by('name').annotate(
> num_episodes=Count('tvepisode'),
> num_episodes_with_media=Count('tvepisode__media_id'),
> num_aired_episodes=Count('tvepisode__airdate'),
> num_seasons=Max('tvepisode__season'))

Woah. Is that new?

[9dev...@gmail.com]

As an aside, this can probably be written as:

  current_user_vote = item.vote_set.filter(user=request.user)

>
> I tried to use annotate() two times, however it filtered out everything
> except items current user voted on.

Just use it once.

Thank you for answer, however I still can't see how can I do it.

Using annotate only once won't let me use filter() to exclude users different than the current one.

Item.objects.annotate(
    score=Sum('votes__value'),
    current_user_vote=Sum('votes__value')
)

[Tom Evans]

It's unclear to me what you are trying to do. Are you trying to
annotate the item with two values, or are you trying to restrict the
values that are used to calculate the annotation, so that only the
current users votes are counted?

If you are trying to restrict the votes counted to just that user,
then this might suit:

Item.objects.filter(votes__user=request.user).annotate(score=Sum('votes__value'))

If you're trying to do both in one query, then I'm not sure that is
possible as you would need to join to the votes table multiple times
with different join conditions. It's technically possible, so you
could easily write it in a Foo.objects.raw() call, but I'm not sure
the API exposes a way to do it neatly.

However, given a specific user and a set of items (or even all items),
it would be trivial to get all their current quotes in one additional
query:

Votes.objects.filter(
user=request.user).values_list(
'item_id', 'value')

Don't waste time on premature optimisation!

Cheers

Tom

[Tom Evans]

No, I think it has always been available like that since aggregation was added.

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.


Cheers

Tom