Databases
StackOverflow2013
Postsdbo
POProblem trying to achieve a join using the `comments` contrib in Django
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POProblem trying to achieve a join using the `comments` contrib in Django
    text
    copied!<p>I have this model, comments are managed with the <code>django_comments</code> contrib:</p> <pre><code>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' ) </code></pre> <p>I want to retrieve <code>Fortune</code> objects with a supplementary <code>nb_comments</code> value for each, counting their respectve number of comments ; I try this query:</p> <pre><code>&gt;&gt;&gt; Fortune.objects.annotate(nb_comments=models.Count('comments')) </code></pre> <p>From the shell:</p> <pre><code>&gt;&gt;&gt; from django_fortunes.models import Fortune &gt;&gt;&gt; from django.db.models import Count &gt;&gt;&gt; Fortune.objects.annotate(nb_comments=Count('comments')) [&lt;Fortune: My first fortune, from NiKo&gt;, &lt;Fortune: Another One, from Dude&gt;, &lt;Fortune: A funny one, from NiKo&gt;] &gt;&gt;&gt; from django.db import connection &gt;&gt;&gt; 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'} </code></pre> <p>Below is the properly formatted sql query:</p> <pre><code>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 </code></pre> <p>Can you spot the problem? Django won't LEFT JOIN the <code>django_comments</code> table with the <code>content_type</code> data (which contains a reference to the <code>fortune</code> one).</p> <p>This is the kind of query I'd like to be able to generate using the ORM:</p> <pre><code>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 </code></pre> <p>But I don't manage to do it, so help from Django veterans would be much appreciated :)</p> <p>Hint: I'm using Django 1.2-DEV</p> <p>Thanks in advance for your help.</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload