Django通过.annotate（）使用order_by并获取相关字段 [英] Django Using order_by with .annotate() and getting related field

问题描述

我有以下数据，

此查询按topicid分组，然后在每个组中获取最大日期，帖子频率并计算作为贡献者的作者数量，

This query groups by topicid, and then in each group gets the max date, frequency of posts and counts the number of authors as contributors,

    info_model = InfoModel.objects.values('topicid')
            .annotate( max=Max('date'), freq=Count('postid'),                   
             contributors=Count('author', distinct=True))

此查询可以如下显示，

Q.1（已解决）如何按以下顺序对行进行排序日期，从最近开始？我确实将.order_by（'date'）附加到查询中，这似乎是最明显的解决方案，但这会产生

Q.1 (SOLVED) How can I order the rows by date, from most recent down? I did appended .order_by('date') to the query, which seems like the most obvious solution but this produces,

完全更改频率和贡献。

编辑：可以实现排序通过附加.order_by（'-max'）

The ordering can be achieved by appending .order_by('-max')

Q.2如何显示该日期的发布？因此帖子列应显示为

Q.2 How can I display the 'post' for that date? So the post column should display,

ya

请参见ya

ciao

yoyo

我认为以下内容应适用于{{item.post}

I thought the following should work with {{ item.post }}, but no such luck.

  <table class='table table-striped table-hover'>
        <thead>
          <tr>
            <th>freq</th>
            <th>topicid</th>
            <th>date</th>
            <th>contributors</th>
            <th>post</th>
          </tr>
        </thead>
        <tbody>
          {% for item in info %}
          <tr>
            <td>{{ item.freq }}</td>
            <td>{{ item.topicid }}</td>
            <td>{{ item.max }}</td>
            <td>{{ item.contributors }}</td>
            <td>{{ item.post }}</td>
          </tr>
          {% endfor %}
        </tbody>
      </table>

谢谢，

编辑：

我可以使用原始SQL来获得正确的结果，但无法通过Django查询完成

I can get the correct result with raw SQL but can't do it wth a Django query,

info_model = list(InfoModel.objects.raw('SELECT *, 
              max(date),  
              count(postid) AS freq,     
              count(DISTINCT author) AS contributors FROM        
              crudapp_infomodel GROUP BY topicid ORDER BY date DESC'))

在此将原始SQL重写为Django查询

推荐答案

以下视图将两个查询合并以解决问题，

The following view amalgamates two queries to solve the problem,

def info(request):
    info_model = InfoModel.objects.values('topic')
                 .annotate( max=Max('date'), 
                 freq=Count('postid'), 
                 contributors=Count('author', distinct=True))
                 .order_by('-max')

    info2 = InfoModel.objects.all()

    columnlist = []
    for item in info2:
         columnlist.append([item])

    for item in info_model:
        for i in range(len(columnlist)):
            if item['max'] == columnlist[i][0].date:
                item['author'] = columnlist[i][0].author
                item['post'] = columnlist[i][0].post
                print item['max']

    paginator = Paginator(info_model, 20)
    page = request.GET.get('page')
    try:
        info = paginator.page(page)
    except PageNotAnInteger:
        # If page is not an integer, deliver first page.
        info = paginator.page(1)
    except EmptyPage:
        # If page is out of range (e.g. 9999), deliver last page of results.
        info = paginator.page(paginator.num_pages)
    return render(request, 'info.html', {'info': info})

这篇关于Django通过.annotate（）使用order_by并获取相关字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！