Using Django ORM for computed field

Using Django
1

Hello - I am somewhat new to Django and trying to learn it. So far I took a course on Real Python and bought and read Will Vincent’s Django for Beginners and Django for Professionals. I am building out a site (mainly for learning). On that site, I have a scoreboard (see site here). Each user post is worth 10 points, and each user comment is worth 1 point. I want to sort displayed users by highest score first. So with raw SQL, it would be:

select username, email,
    ( select 10 * count(*) from posts_post where accounts_customuser.id = posts_post.author_id ) +
    ( select count(*) from posts_comment where accounts_customuser.id = posts_comment.author_id ) total 
    from accounts_customuser
    order by total desc, username;

Of course, the idea with Django is to leverage the ORM and avoid raw SQL. I see there are aggregates, so it’s close to something like this:

queryset = get_user_model().objects.annotate(post_count=Count('posts'),
           comment_count=Count('comment')).order_by('-post_count', '-comment_count', 'username')

However, that’s not quite right because if I have a user with 1 post and 1 comment (11 points) and then one with 0 posts and 12 comments (12 points), they wouldn’t be sorted right by the ORM with the above QuerySet. I tried leveraging F:

# Note:  CustomUser is what get_user_model() returns
CustomUser.objects.annotate(post_count=Count('posts'), comment_count=Count('comment'),
        total=Sum(F('post_count') * 10 + F('comment_count'))
    ).order_by('-post_count', '-comment_count', 'username')

However, that throws the following error:

FieldError: Cannot compute Sum('<CombinedExpression: F(post_count) * Value(10) +
    F(comment_count)>'): '<CombinedExpression: F(post_count) * Value(10) + 
    F(comment_count)>' is an aggregate

I’ve been working my way through the Django ORM docs, but I’m not making progress and I’m not sure where to go from here. Could someone point me in the right direction? Pointing me at books or documentation is great, as long as it has good examples showing how to build similar things.

Thank you,
–Jim

2

You’re really close - you don’t need the Sum function on line two:

CustomUser.objects.annotate(
    post_count=Count('posts'), 
    comment_count=Count('comment'),
    total=F('post_count') * 10 + F('comment_count')
).order_by('-post_count', '-comment_count', 'username')

post_count and comment_count are singular values, not a list - there’s nothing to “Sum” here.

1 Like