Problem trying to achieve a connection using the `comments` input in Django
I have this model, comments are managed with 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 get objects Fortune
with an additional value nb_comments
for each, counting their respectable number of comments; I am trying 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 a 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 identify the problem? Django will NOT HELP a django_comments
data table content_type
(which contains a link to Fortune
).
Here is the query I would like to create 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 am failing, so help from Django veterans would be greatly appreciated :)
Hint: I am using Django 1.2-DEV
Thanks in advance for your help.
a source to share
Why do you want to join the content type table? The original request is incorrect, but not for this reason. The content type reference is the identification of the target model that the comments are associated with, but you already know that since you are only selecting comments associated with Fortune
. Django has already requested the model ContentType
to get the value for Fortune
, so just add one clause WHERE
:
... WHERE django_comments_comment.content_type_id = xx
You might be able to do it right by adding the ORM equivalent:
...filter(comment__content_type=ContentType.objects.get_for_model(Fortune))
although this seems to be a mistake that Django does not automatically make.
a source to share