Annotate querying, pk

Question

How should I query when I use pk with annotate?

I need to redirect to user profile when a guest click in link.

Models

class Posty(models.Model):
   title = models.CharField(max_length=250, blank=False, null=False, unique=True)
   sub_title = models.SlugField(max_length=250, blank=False, null=False, unique=True)
   content = models.TextField(max_length=250, blank=False, null=False)
   image = models.ImageField(default="avatar.png",upload_to="images", validators=[FileExtensionValidator(['png','jpg','jpeg'])])
   author = models.ForeignKey(Profil, on_delete=models.CASCADE)
   updated = models.DateTimeField(auto_now=True)
   published = models.DateTimeField(auto_now_add=True)
   T_or_F = models.BooleanField(default=False)
   likes = models.ManyToManyField(Profil, related_name='liked')
   unlikes = models.ManyToManyField(Profil, related_name='unlikes')
   created_tags = models.ForeignKey('Tags', blank=True, null=True, related_name='tagi', on_delete=models.CASCADE)

class CommentPost(models.Model):
   user = models.ForeignKey(Profil, on_delete=models.CASCADE)
   post = models.ForeignKey(Posty, on_delete=models.CASCADE, related_name="comments")
   content1 = models.TextField(max_length=250, blank=False, null=False)
   date_posted = models.DateTimeField(default=timezone.now)
   date_updated = models.DateTimeField(auto_now=True)

def __str__(self):
    return str(self.content1)

class Profil(models.Model):
   first_name = models.CharField(max_length=50)
   last_name = models.CharField(max_length=50)
   age = models.PositiveIntegerField(blank=True, null=True)
   country = models.CharField(max_length=50)
   user = models.OneToOneField(Account, on_delete=models.CASCADE)
   avatar = models.ImageField(default="avatar.png", upload_to="images",validators=[FileExtensionValidator(['png', 'jpg', 'jpeg'])])
   slug = models.SlugField(blank=True)
   gender = models.CharField(max_length=6, choices=GENDER)
   friends = models.ManyToManyField(Account, blank=True, related_name="Friends")
   social_facebook = models.CharField(max_length=250, null=True, blank=True)
   social_instagram = models.CharField(max_length=250, null=True, blank=True)
   social_twitter = models.CharField(max_length=250, null=True, blank=True)
   social_youtube = models.CharField(max_length=250, null=True, blank=True)
   social_linkedin = models.CharField(max_length=250, null=True, blank=True)
   updated = models.DateTimeField(auto_now=True)
   created = models.DateTimeField(auto_now_add=True)

Views

   if tag == None:
    my_tag = Posty.objects.annotate(
        latest_comment = Subquery(CommentPost.objects.filter(post=OuterRef('id')).values('content1').order_by('-date_posted')[:1]),
        my_author=Subquery(CommentPost.objects.filter(post=OuterRef('id')).values('user__user__username').order_by('-date_posted')[:1]),
    )

I got a correct username, but I can't get a correct redirect:

Answer 1

Unfortunately, you need to annotate the CommentPost's id information as well with the queryset just like you added the username information. That will end up being a lot of subquery added to the original queryset. Rather I would suggest to use prefetch related to preload the CommentPost information with the original queryset (for reducing DB hits) and directly access that information from template. For example:

# view
my_tag = Posty.objects.prefetch_related('commentpost_set')

# template
{% for post in my_tag %}
    <span><a href="{% url 'home:detail_post' post.pk %}"> {{post.commentpost_set.last.content1}}</a><span>
    <span><a href="{% url 'home:detail_post' post.commentpost_set.last.user.pk %}"> {{post.commentpost_set.last.user.username}}</a><span>
{% endfor %}

Also, last is a queryset function which returns the last object of a given queryset. I can access the CommentPost queryset from a Posty object by reverse querying.