[FIXED] Return only an aggregate count instead of the full record for a ManyToMany relationship query

Issue

Have a few tables for which I successfully built export to CSV, based on the Models. However, for one where I count the 'likes' for the news (posts) I'm getting nowhere.

Here is my model:

class News(models.Model):
    news_title = models.CharField(max_length=300)
    news_text = models.TextField(max_length=2000)
    news_author = models.CharField(max_length=150)
    news_date = models.DateField(default=now)
    likes = models.ManyToManyField(User, related_name='user_like', blank=True)

    @property
    def total_likes(self):
        return self.likes.count()

Problem is, that I can print out to CSV all items from the model, but if I print "likes" I get duplicated (or more) rows in CSV. The reason is, that if News is liked by 3 users, I get 3x rows on CSV for each User and under "Like Count" column I get their IDs.

What I would like to get instead is: 1x row per News with a total of likes for each news.

and view.py

@login_required
def export_news(request):
    newss = News.objects.all()
    response = HttpResponse(content_type='txt/csv')
    writer = csv.writer(response)
    writer.writerow(["ID","Title","Author","Date","Text","Likes Count"])
    
    for news in newss.values_list('id','news_title','news_author','news_date','news_text','likes'):
        writer.writerow(news)
        
    response['Content-Disposition'] = 'attachment; filename="News_list.csv"'

    return response

Appreciate any help. Thx

Solution

You need to annotate the likes count on the newss queryset and then call that aggregated value in the values_list. Something like:

from django.db.models import Count

newss = News.objects.annotate(num_likes=Count('likes')).all()
...
for news in newss.values_list('id','news_title','news_author','news_date','news_text','num_likes'):
    writer.writerow(news)

Your issue here is that a values_list query will indeed return a list item for each and every matching record, just as if you made this query in SQL. Because you are returning likes, which is not a 1:1 field, you can likely expect to have more than one line per news item for this query. If you just want the rolled up aggregate, you need to specify that in the query via an annotation, and then only return the annotated value, not the full likes record from the query.

Answered By - Ashley H.