Django how to annotate count nested forloop queryset dictionary

Question

Good Day,

My Problem is:

my project depending on giving points to users who make any actions like (post, comment, favorite, likes ......).

So, in users list page i want to list all users and other data for each user (name, points, badges, ....)

to give the users points i have to count his posts, comments, likes, and so on.....

i tried several methods and ways but all is failed to get annotate or prefetch_related or select_related

Models.py

class Post(models.Model):
    title = models.CharField(max_length=100)
    content = models.TextField()
    author = models.ForeignKey(User, on_delete=models.CASCADE, related_name='posts')

Views.py

def user_list(request):
    users = User.objects.all()
    template = 'user/users_list.html'

    nested_posts = {}
    for user in users:
        posts = user.posts.all()
        nested_posts[user, posts] = posts.count()

    print("nested : ", nested_posts)

    context = {
        'users': users,
        'user':user,
        'posts': posts,
        'nested_posts': nested_posts,}
    return render(request, template, context)

when i print nested .. i found the count of every user' posts .. but how can i make it as variable to re-use in calculated field

QuerySet

nested :  {(<User: Fareed>, <QuerySet [<Post: Senior Purchasing Specialist>]>): 1, 
(<User: Hussein>, <QuerySet [<Post: Senior Software Development Engineer in Test>]>): 1, 
(<User: Karima>, <QuerySet []>): 0, 
(<User: Yahia>, <QuerySet []>): 0}

and i also tried :

GetUserID = User.objects.get(id=2)
var01 = GetUserID.posts.all().count()

but this was for one user with (id=2) .. and sure all users got the total posts of user(id=2) not for each of them.

and i also tried :

Posts_count_per_user = User.posts.annotate(posts_count=Count('posts'))
User_Score_of_posts = Posts_count_per_user.aggregate(posts_score=Count('posts_count') * 1000)

but i got this error:

'ReverseManyToOneDescriptor' object has no attribute 'annotate'

any suggestions please ...

Thanks in advance,

Answer 1

Have you tried this,

user_qs = User.objects.annotate(posts_count=Count('posts'))

# usage
for user_instance in user_qs:
    print("post count: ", user_instance.posts_count)
    print("post score: ", user_instance.posts_count * 1000)

Or you can annotate the post score in the DB level itself,

from django.db.models import F, Count

user_qs = User.objects.annotate(posts_count=Count('posts'), posts_score=F('posts_count') * 1000)