Login

Annotate queryset with comment count

Author:
fivethreeo
Posted:
October 1, 2008
Language:
Python
Version:
1.0
Score:
1 (after 1 ratings)

Code example

queryset = Event.objects.all().select_related('user', 'category')

queryset = comments_extra_count(queryset)
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
from django.contrib.contenttypes.models import ContentType
from django.contrib.comments.models import Comment
from django.db import connection

qn = connection.ops.quote_name

def qf(table, field): # quote table and field
    return '%s.%s' % ( qn(table), qn(field) )

def comments_extra_count(queryset):
    
    commented_model = queryset.model
    contenttype = ContentType.objects.get_for_model(commented_model)
    commented_table = commented_model._meta.db_table
    comment_table = Comment._meta.db_table
    
    sql = '''SELECT COUNT(*) FROM %s
        WHERE %s=%%s AND %s=%s
    ''' % (
        qn(comment_table),
        qf(comment_table, 'content_type_id'),
        qf(comment_table, 'object_pk'),
        qf(commented_table, 'id')
    )
    
    return queryset.extra(
        select={'comment_count': sql },
        select_params=(contenttype.pk,)
    )

More like this

  1. Template tag - list punctuation for a list of items by shapiromatron 2 months ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 2 months, 1 week ago
  3. Serializer factory with Django Rest Framework by julio 9 months, 1 week ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 9 months, 4 weeks ago
  5. Help text hyperlinks by sa2812 10 months, 3 weeks ago

Comments

kioopi (on October 8, 2009):

That's pretty nifty. Will keep me from de-normalizing comment-counts for a while.

#

Please login first before commenting.