Ordering entries via comment count with django

Question

I need to get entries from database with counts of comments. Can i do it with django's comment framework? I am also using a voting application which is not using GenericForeignKeys i get entries with scores like this:

class EntryManager(models.ModelManager):
    def get_queryset(self):
        return super(EntryManager,self).get_queryset(self).all().annotate(\
            score=Sum("linkvote__value"))

But when there is foreignkeys i am being stuck. Do you have any ideas about that?

extra explaination: i need to fetch entries like this:

id | body | vote_score | comment_score |
 1 |  foo |         13 |             4 |
 2 |  bar |          4 |             1 |

after doing that, i can order them via comment_score. :)

Thans for all replies.

annotating with comment_count=Count('comment') doesn't work? .. (you better check what the db field of the comments are.. dont know django comments from memory.. — niklasdstrom, Nov 21 '11 at 17:16
Check out [this](https://github.com/coleifer/django-generic-aggregation) — jperelli, Jan 26 '12 at 01:49

score 1 · Accepted Answer · answered Dec 14 '11 at 20:57

Apparently, annotating with reverse generic relations (or extra filters, in general) is still an open ticket (see also the corresponding documentation). Until this is resolved, I would suggest using raw SQL in an extra query, like this:

return super(EntryManager,self).get_queryset(self).all().annotate(\
    vote_score=Sum("linkvote__value")).extra(select={
        'comment_score': """SELECT COUNT(*) FROM comments_comment
            WHERE comments_comment.object_pk = yourapp_entry.id
            AND comments_comment.content_type = %s"""
    }, select_params=(entry_type,))

Of course, you have to fill in the correct table names. Furthermore, entry_type is a "constant" that can be set outside your lookup function (see ContentTypeManager):

from django.contrib.contenttypes.models import ContentType
entry_type = ContentType.objects.get_for_model(Entry)

This is assuming you have a single model Entry that you want to calculate your scores on. Otherwise, things would get slightly more complicated: you would need a sub-query to fetch the content type id for the type of each annotated object.

Ordering entries via comment count with django

1 Answers1