0

I have a Users table which is FK to a table called Post. How can I get only the last Post that the user registered? The intention is to return a list of users with the last registered post, but when obtaining the users, if the user has 3 posts, the user is repeated 3 times. I'm interested in only having the user once. Is there an alternative that is not unique?


class User(models.Model):
    name = models.CharField(max_length=50)

class Post(models.Model):
    title = models.CharField(max_length=50)
    user = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts', related_query_name='posts')
    created = models.DateTimeField(default=timezone.now)

    class Meta:
        get_latest_by = 'created'
        ordering = ['-created']`

I already tried with selected_related and prefetch_related, I keep getting multiple user registrations when they have multiple Posts.

user = User.objects.select_related('posts').all().values_list('id', 'name', 'posts__title', 'posts__created')

This does give me the answer I want, but when I change the created field to sort by date, I don't get the newest record, I always get the oldest.

user = User.objects.select_related('posts').all().values_list('id', 'name', 'posts__title', 'posts__created').distinct('id')

I'm trying to do it without resorting to doing a record-by-record for and getting the most recent Post. I know that this is an alternative but I'm trying to find a way to do it directly with the Django ORM, since there are thousands of records and a for is less than optimal.

danic
  • 1
  • 2
  • Not an exact duplicate, but see this question: https://stackoverflow.com/questions/30528268/annotate-with-latest-related-object-in-django – Selcuk Nov 24 '22 at 01:28

1 Answers1

0

In that case your Django ORM query would first filter posts by user then order by created in descending order and get the first element of the queryset.

last_user_post = Post.objects.filter(user__id=1).order_by('-created').first()

Alternatively, you can use an user instance:

user = User.objects.get(id=1)
last_user_post = Post.objects.filter(user=user).order_by('-created').first()
Niko
  • 3,012
  • 2
  • 8
  • 14
  • That answer is correct, but what I try to avoid is doing a for or some loop to get individual, since there are thousands of records that I try to get and I would like to do it directly with the Django ORM. – danic Nov 24 '22 at 00:56
  • I don't see any other way with the current model. In order to be sure you have the latest post you need to check your records. Nonetheless, in order to avoid the loop you can add an extra field to your User model 'latest_post_id' for example, and every time the user do a Post you save that post id into this field. (It could even be the last X in a list [1,2,3...X]) – Niko Nov 24 '22 at 04:45