使用queryset.extra()按评论计数和日期对queryset进行排序和限制(django) [英] Sort and limit queryset by comment count and date using queryset.extra() (django)

问题描述

我试图根据每个对象具有的评论数以及发布评论的时间范围来排序/缩小对象的查询集.我使用的是queryset.extra()方法(使用使用通用外键的django_comments).

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).

我有了使用queryset.extra()(和代码)的想法从这里.这是昨天我最初的问题的后续问题 (这表明我正在取得一些进展).

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).

到目前为止，我的工作方式是按照评论数进行排序；但是，我想扩展功能，还能够传递时间范围参数(例如7天)并返回该时间范围内评论最多的帖子的有序列表.

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)

我尝试过的事情:

我不太熟悉SQL，但是我确实尝试过手动添加另一个WHERE条件，以查看是否可以取得一些进步:

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?

感谢您的帮助或见识.

推荐答案

尝试一下[已更新为包括时差(cutoff_date)]

Try this [updated to include time difference (cutoff_date) ]

 queryset = queryset.extra(select={
            'comment_count' : """
                SELECT COUNT(*)
                FROM django_comments
                WHERE
                    django_comments.content_type_id=%s AND
                    django_comments.object_pk=%s.%s AND
                    django_comments.submit_date < '%s'
            """ % ( ctype.pk, 
                    queryset.model._meta.db_table, 
                    queryset.model._meta.pk.name,
                    cutoff_date ),
            }).order_by('-comment_count', '-created')

为了获得良好的截止日期，我建议使用 python- dateutil 模块:

To get a good cutoff_date, I'd recommend using the awesome-in-a-box relativedelta from python-dateutil module:

from datetime import relativedelta
from datetime import date

cutoff_date = date.today() - relativedelta(weeks =1) #relativedelta does all the heavy lifting

这篇关于使用queryset.extra()按评论计数和日期对queryset进行排序和限制(django)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！