Django .order_by() related field returns too many items-CodePudding

I'm trying to return a list of users that have recently made a post, but the order_by method makes it return too many items.

there is only 2 accounts total, but when I call

test = Account.objects.all().order_by('-posts__timestamp')
[print(i) for i in test]

it will return the author of every post instance, and its duplicates. Not just the two account instances.

[email protected]
[email protected]
[email protected]
[email protected]
[email protected]

Any help?

class Account(AbstractBaseUser):
    ...

class Posts(models.Model):
    author = models.ForeignKey('accounts.Account',on_delete=models.RESTRICT, related_name="posts")
    timestamp   = models.DateTimeField(auto_now_add=True)
    title = ...
    content = ...

CodePudding user response：

This is totally normal. You should understand how is the SQL query generated. Yours should look something like that:

select *
from accounts
  left join post on post.account_id = account.id
order by post.timestamp

You are effectively selecting every post with its related users. It is normal that you have some duplicated users.

What you could do is ensure that your are selecting distinct users: Account.objects.order_by('-posts__timestamp').distinct('pk')

What I would do is cache this information in the account (directly on the acount model or in another model that has a 1-to-1 relashionship with your users. Adding a last_post_date to your Account model would allow you to have a less heavy request.

Updating the Account.last_post_date every time a Post is created can be a little tedious, but you can abstract this by using django models signals.