Django: problem with merging querysets after annotation

Question

Hi

I have a manager for "Dialog" looking like this:

class AnnotationManager(models.Manager):
def get_query_set(self):
    return super(AnnotationManager, self).get_query_set().annotate(
        num_votes=Count('vote', distinct=True),
        num_comments=Count('comment', distinct=True),
        num_commentators = Count('comment__user', distinct=True),
    )

Votes and Comments has a ForeignKey to Dialog. Comments has a ForeignKey to User. When I do this:

dialogs_queryset = Dialog.public.filter(organization=organization)
dialogs_popularity = dialogs_queryset.exclude(num_comments=0) | dialogs_queryset.exclude(num_votes=0)

...dialogs_popularity will never returned the combination, but only the dialogs with more than 0 comments, or if I change the order of the OR, the dialogs with more than 0 votes!

To me, the expected behavior would be to get the dialogs with more than 0 votes AND the dialogs with more than 0 comments.

What am I missing? Or is there a bug in the annotation behavior here?

Answer 1

Did want dialogs with both votes and comments?

# must have both a vote and a comment
# aka.  has_comments_and_votes = has_comments AND has_votes
#                              = !(has_no_comments OR has_no_votes)
has_comments = ~Q(num_comments=0)
has_votes = ~Q(num_votes=0)

dialogs_queryset.filter(num_comments__ne=0, num_votes__ne=0)
# or with Q objects
dialogs_queryset.filter(has_comments & has_votes)
dialogs_queryset.exclude(~has_comments | ~has_votes)

Or dialogs having either votes, comments or both. (What you want based on comment.)

# must have at least 1 vote or 1 comment
# aka. has_comments_or_votes = has_comments OR has_votes
#                            = !(has_no_comments AND has_no_votes)
dialogs_queryset.exclude(num_comments=0, num_votes=0)
# again with Q objects
dialogs_queryset.filter(has_comments | has_votes)  # easiest to read!
dialogs_queryset.exclude(~has_comments & ~has_votes)

I added the Q objects examples because the "|" in your code sample seemed to be hinting at them and they make it easier to create ORed queries.

EDIT: I added has_comments and has_votes to make things a little easier to read.