Geek asks and answers

Django: sort query set by sum of annotated fields?

I am creating a Django site for discussion. Users can participate in discussions, as well as vote for approval for discussions and posts during discussions. A simplified data model is as follows:

class Discussion:
    name = models.CharField(max_length=255)

class Message:
    owner = models.ForeignKey(User, related_name='messages')
    body = models.TextField()
    discussion = models.ForeignKey(Discussion, related_name='messages')

class MessageApprovalVote:
    owner = models.ForeignKey(User, related_name='message_approval_votes')
    message = models.ForeignKey(Message, related_name='approval_votes')

class DiscussionApprovalVote:
    owner = models.ForeignKey(User, related_name='discussion_approval_votes')
    discussion = models.ForeignKey(Discussion, related_name='approval_votes')

I want to choose the 20 most active “discussions”, which means ordering by the sum of the number of messages, the total number of votes for approving messages and the number of votes for approving discussions for this discussion or

# Doesn't work
Discussion.objects.
    order_by(Count('messages') + 
             Count('approval_votes') + 
             Count('messages__approval_votes'))

Using annotations, I can calculate the totals of each of three factors:

scores = Discussion.objects.annotate(
    total_messages=Count('messages', distinct=True),
    total_discussion_approval_votes=Count('approval_votes', distinct=True),
    total_message_approval_votes=Count('messages__approval_votes', distinct=True))

Then I thought something happened when I found the method extra:

total_scores = scores.extra(
    select={
        'score_total': 'total_messages + total_discussion_approval_votes + total_message_approval_votes'
    }
)

and then be able to:

final_answer = total_scores.order_by('-score_total')[:20]

but the call extragives DatabaseError:

DatabaseError: column "total_messages" does not exist
LINE 1: SELECT (total_votes + total_messages + total_persuasions) AS...

. extra annotate d? , , SQL-? Postgres, .

!

+5
2

, SQL- . score_total , .

SQL , , Django. Django- , , SQLite:

SELECT  id, name,
    total_messages, total_discussion_approval_votes, total_message_approval_votes,
    (total_messages +
     total_discussion_approval_votes +
     total_message_approval_votes) as score_total
FROM
   (SELECT
    discussion.id,
    discussion.name,
    COUNT(DISTINCT discussionapprovalvote.id) AS total_discussion_approval_votes,
    COUNT(DISTINCT messageapprovalvote.id) AS total_message_approval_votes,
    COUNT(DISTINCT message.id) AS total_messages
    FROM discussion
    LEFT OUTER JOIN discussionapprovalvote
         ON (discussion.id = discussionapprovalvote.discussion_id)
    LEFT OUTER JOIN message
         ON (discussion.id = message.discussion_id)
    LEFT OUTER JOIN messageapprovalvote
         ON (message.id = messageapprovalvote.message_id)
    GROUP BY discussion.id, discussion.name)

ORDER BY score_total DESC
LIMIT 20;
+4

F:

Discussion.objects.annotate(
    total_messages=Count('messages', distinct=True),
    total_discussion_approval_votes=Count('approval_votes', distinct=True),
    total_message_approval_votes=Count('messages__approval_votes', distinct=True)),
    total_score=F('total_messages') + F('total_discussion_approval_votes') + F('total_message_approval_votes')
).order_by('total_score')
0

More articles:


All Articles