Sort and limit queryset by comment count and date using queryset.extra() (django)
- by thornomad
I am trying to sort/narrow a queryset of objects based on the number of comments each object has as well as by the timeframe during which the comments were posted. Am using a queryset.extra() method (using django_comments which utilizes generic foreign keys).
I got the idea for using queryset.extra() (and the code) from here. This is a follow-up question to my initial question yesterday (which shows I am making some progress).
Current Code:
What I have so far works in that it will sort by the number of comments; however, I want to extend the functionality and also be able to pass a time frame argument (eg, 7 days) and return an ordered list of the most commented posts in that time frame.
Here is what my view looks like with the basic functionality in tact:
import datetime
from django.contrib.comments.models import Comment
from django.contrib.contenttypes.models import ContentType
from django.db.models import Count, Sum
from django.views.generic.list_detail import object_list
def custom_object_list(request, queryset, *args, **kwargs):
'''Extending the list_detail.object_list to allow some sorting.
Example: http://example.com/video?sort_by=comments&days=7
Would get a list of the videos sorted by most comments in the
last seven days.
'''
try: # this is where I started working on the date business ...
days = int(request.GET.get('days', None))
period = datetime.datetime.utcnow() - datetime.timedelta(days=int(days))
except (ValueError, TypeError):
days = None
period = None
sort_by = request.GET.get('sort_by', None)
ctype = ContentType.objects.get_for_model(queryset.model)
if sort_by == 'comments':
queryset = queryset.extra(select={
'count' : """
SELECT COUNT(*) AS comment_count
FROM django_comments
WHERE
content_type_id=%s AND
object_pk=%s.%s
""" % ( ctype.pk, queryset.model._meta.db_table,
queryset.model._meta.pk.name ),
},
order_by=['-count']).order_by('-count', '-created')
return object_list(request, queryset, *args, **kwargs)
What I've Tried:
I am not well versed in SQL but I did try just to add another WHERE criteria by hand to see if I could make some progress:
SELECT COUNT(*) AS comment_count
FROM django_comments
WHERE
content_type_id=%s AND
object_pk=%s.%s AND
submit_date='2010-05-01 12:00:00'
But that didn't do anything except mess around with my sort order.
Any ideas on how I can add this extra layer of functionality?
Thanks for any help or insight.
