Django annotate query set with a count on subquery

Question

This doesn't seem to work in django 1.1 (I believe this will require a subquery, therefore comes the title)

qs.annotate(interest_level= \
             Count(Q(tags__favoritedtag_set__user=request.user))
           )

There are items in my query set which are tagged and tags can be favorited by users, I would like to calculate how many times a user had favorited each item in the set via tags.

is there a way to construct a query like this without using extra()?

Thanks.

Answer 1

Looking at the add_aggregate function within django/db/models/sql/query.py, query objects will not be accepted as input values.

Unfortunately, there is currently no direct way within Django to aggregate/annotate on what amounts to a queryset, especially not one that is additionally filtered somehow.

Assuming the following models:

class Item(models.Model):
    name = models.CharField(max_length=32)

class Tag(models.Model):
    itemfk = models.ForeignKey(Item, related_name='tags')
    name = models.CharField(max_length=32)

class FavoritedTag(models.Model):
    user = models.ForeignKey(User)
    tag = models.ForeignKey(Tag)

Also, you cannot annotate a queryset on fields defined via .extra().

One could drop into SQL in views.py like so:

from testing.models import Item, Tag, FavoritedTag
from django.shortcuts import render_to_response
from django.contrib.auth.decorators import login_required
from django.utils.datastructures import SortedDict

@login_required
def interest_level(request):
    ruid = request.user.id

    qs = Item.objects.extra(
        select = SortedDict([
            ('interest_level', 'SELECT COUNT(*) FROM testing_favoritedtag, testing_tag \
            WHERE testing_favoritedtag.user_id = %s \
            AND testing_favoritedtag.tag_id = testing_tag.id \
            AND testing_tag.itemfk_id = testing_item.id'),
        ]),
        select_params = (str(ruid),)
    )

    return render_to_response('testing/interest_level.html', {'qs': qs})

Template:

{% for item in qs %}
    name: {{ item.name }}, level: {{ item.interest_level }}<br>
{% endfor %}

I tested this using MySQL5. Since I'm no SQL expert though, I'd be curious as to how to optimize here, or if there is another way to "lessen" the amount of SQL. Maybe there is some interesting way to utilize the related_name feature here directly within SQL?

Answer 2

If you want to avoid dropping to raw SQL, another way to skin this cat would be to use a model method, which will then give you a new attribute on the model to use in your templates. Untested, but something like this on your Tags model should work:

class Tag(models.Model):
    itemfk = models.ForeignKey(Item, related_name='tags')
    name = models.CharField(max_length=32)

    def get_favetag_count(self):
        """
        Calculate the number of times the current user has favorited a particular tag
        """

        favetag_count = FavoritedTag.objects.filter(tag=self,user=request.user).count()
        return favetag_count

Then in your template you can use something like :

{{tag}} ({{tag.get_favetag_count}})

The downside of this approach is that it could hit the database more if you're in a big loop or something. But in general it works well and gets around the inability of annotate to do queries on related models. And avoids having to use raw SQL.