Rewrite raw SQL as Django query

Question

I am trying to write this raw SQL 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'))

as a django query. The following attempt does not work as I can't get related fields for 'author' and 'post'.

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

With raw SQL I can use SELECT * but how can I do the equivalent with the Django query?

The model is,

class InfoModel(models.Model):
    topicid = models.IntegerField(default=0)
    postid = models.IntegerField(default=0)
    author = models.CharField(max_length=30)
    post = models.CharField(max_length=30)
    date = models.DateTimeField('date published')

I did previously post this problem here Django Using order_by with .annotate() and getting related field

Fictive

I guess you're using MySQL since your raw query won't work on PostGres. The reason is you're ordering with a field that is not is the group by. Your raw query is strange, please provide an input and an expected output – Conlen

Ok I see what you mean, so I need to use .order_by('-max'). This orders the data correctly. You can actually see the original data if you follow the link at the bottom of the post. – Fictive

I am using sqlite. – Fictive

So the issue now is how do I get the related fields for 'author' and 'post'? Thanks for your help. – Fictive

Please explain more what you want – Conlen

I would like to display the 'author and 'post' field value in the database row of the max date. Thanks – Fictive

I am actually trying to get the 'post' value where date = max. So how do i query that? – Fictive

Answer 1

I guess you want to order by the maximum date so:

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

Conlen

That order_by('-max') works correctly, thanks. But I can't show the other related fields like I can with the SQL (or maybe it is mySQL, I hardly know the difference). – Fictive

I have written a solution here #37908549 – Fictive

Answer 2

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

    return render(request, 'info.html', {'info_model': info_model})

Fictive