Django Query values_list getting last value

Question

Lets say I have a blog and a class user in a model. Furthermore I have a class comment connected with a foreign key.

class User(models.Model):
    UserName = models.CharField(max_length=50, blank=True)
    UserCountry = models.CharField(max_length=2, blank=True)

class Comment(models.Model):
    commentText = models.TextField(max_length=1000)
    commentSub = models.ForeignKey(User, related_name='comLink')
    created_at = models.DateTimeField(auto_now_add=True)

Now I want to make an csv export in model admin and a I have a queryset with values_list.

I am wondering whether there exists a possibility to get each User once and e.g. only the last comment?

myList = queryset.values_list('UserName', 'UserCountry', 'comLink__commentText')

comLink is the related name. Now I just want the last comment. A timestamp is existing and I have not figured out how to filter or reverse etc.

Answer 1

You can do it with Subquery, I don`t know your model design, so it would be approximately like that:

from django.db.models import OuterRef, Subquery

com = Comment.objects.filter(commentSub=OuterRef('pk')).order_by('-created_at')
myList = queryset.annotate(LastComment=Subquery(com.values('commentText')[:1]))
myList = myList.values_list('UserName', 'UserCountry', 'LastComment')

https://docs.djangoproject.com/en/2.0/ref/models/expressions/#subquery-expressions