Problem trying to achieve a join using the `comments` contrib in Django

Question

Hi, Django rookie here.

I have this model, comments are managed with the django_comments contrib:

class Fortune(models.Model):
    author = models.CharField(max_length=45, blank=False)
    title = models.CharField(max_length=200, blank=False)
    slug = models.SlugField(_('slug'), db_index=True, max_length=255, unique_for_date='pub_date')
    content = models.TextField(blank=False)
    pub_date = models.DateTimeField(_('published date'), db_index=True, default=datetime.now())
    votes = models.IntegerField(default=0)
    comments = generic.GenericRelation(
        Comment,
        content_type_field='content_type',
        object_id_field='object_pk'
    )

I want to retrieve Fortune objects with a supplementary nb_comments value for each, counting their respectve number of comments ; I try this query:

>>> Fortune.objects.annotate(nb_comments=models.Count('comments'))

From the shell:

>>> from django_fortunes.models import Fortune
>>> from django.db.models import Count
>>> Fortune.objects.annotate(nb_comments=Count('comments'))
[<Fortune: My first fortune, from NiKo>, <Fortune: Another One, from Dude>, <Fortune: A funny one, from NiKo>]
>>> from django.db import connection
>>> connection.queries.pop()
{'time': '0.000', 'sql': u'SELECT "django_fortunes_fortune"."id", "django_fortunes_fortune"."author", "django_fortunes_fortune"."title", "django_fortunes_fortune"."slug", "django_fortunes_fortune"."content", "django_fortunes_fortune"."pub_date", "django_fortunes_fortune"."votes", COUNT("django_comments"."id") AS "nb_comments" FROM "django_fortunes_fortune" LEFT OUTER JOIN "django_comments" ON ("django_fortunes_fortune"."id" = "django_comments"."object_pk") GROUP BY "django_fortunes_fortune"."id", "django_fortunes_fortune"."author", "django_fortunes_fortune"."title", "django_fortunes_fortune"."slug", "django_fortunes_fortune"."content", "django_fortunes_fortune"."pub_date", "django_fortunes_fortune"."votes" LIMIT 21'}

Below is the properly formatted sql query:

SELECT "django_fortunes_fortune"."id", 
       "django_fortunes_fortune"."author", 
       "django_fortunes_fortune"."title", 
       "django_fortunes_fortune"."slug", 
       "django_fortunes_fortune"."content", 
       "django_fortunes_fortune"."pub_date", 
       "django_fortunes_fortune"."votes", 
       COUNT("django_comments"."id") AS "nb_comments" 
FROM "django_fortunes_fortune" 
LEFT OUTER JOIN "django_comments" 
    ON ("django_fortunes_fortune"."id" = "django_comments"."object_pk") 
GROUP BY "django_fortunes_fortune"."id", 
         "django_fortunes_fortune"."author", 
         "django_fortunes_fortune"."title", 
         "django_fortunes_fortune"."slug", 
         "django_fortunes_fortune"."content", 
         "django_fortunes_fortune"."pub_date", 
         "django_fortunes_fortune"."votes" 
LIMIT 21

Can you spot the problem? Django won't LEFT JOIN the django_comments table with the content_type data (which contains a reference to the fortune one).

This is the kind of query I'd like to be able to generate using the ORM:

SELECT "django_fortunes_fortune"."id", 
       "django_fortunes_fortune"."author", 
       "django_fortunes_fortune"."title", 
       COUNT("django_comments"."id") AS "nb_comments" 
FROM "django_fortunes_fortune" 
    LEFT OUTER JOIN "django_comments" 
        ON ("django_fortunes_fortune"."id" = "django_comments"."object_pk") 
    LEFT OUTER JOIN "django_content_type" 
        ON ("django_comments"."content_type_id" = "django_content_type"."id") 
GROUP BY "django_fortunes_fortune"."id", 
         "django_fortunes_fortune"."author", 
         "django_fortunes_fortune"."title", 
         "django_fortunes_fortune"."slug", 
         "django_fortunes_fortune"."content", 
         "django_fortunes_fortune"."pub_date", 
         "django_fortunes_fortune"."votes" 
LIMIT 21

But I don't manage to do it, so help from Django veterans would be much appreciated :)

Hint: I'm using Django 1.2-DEV

Thanks in advance for your help.

Answer 1

Why do you want to join the content type table? The original query is wrong, but not for that reason. The reference to content type is to identify which target model the comments are associated with - but you already know that, since you're only selecting the comments associated with Fortune. Django will have already queried the ContentType model to get the value for Fortune, so should simply be adding a single WHERE clause:

... WHERE django_comments_comment.content_type_id = xx

You might be able to make this work properly by adding the ORM equivalent of that:

...filter(comment__content_type=ContentType.objects.get_for_model(Fortune))

although it does seem to be a bug that Django is not doing this automatically.

Answer 2

It's a bug in Django, see ticket #10870. You'll have to wait for Django 1.3 at least or fix it by yourself.

Answer 3

http://charlesleifer.com/blog/generating-aggregate-data-across-generic-relations/ http://djangosnippets.org/snippets/2034/

UPDATE: http://github.com/coleifer/django-generic-aggregation/