id	summary	reporter	owner	description	type	status	component	version	severity	resolution	keywords	cc	stage	has_patch	needs_docs	needs_tests	needs_better_patch	easy	ui_ux
27144	annotating across a ManyToManyField returns duplicate results	Seti	nobody	"
Dublication objects, if using annotation with models.F() (not the models.Count - it is working properly) on ManyToManyField

Code:

{{{

print('Real count books =\t', Book.objects.count())
print('Total count tags in all books =\t', 
    Book.objects.annotate(
        count_tags=models.Count('tags', distinct=True)
    ).aggregate(
            total_used_count_tags=models.Sum('count_tags')
    )['total_used_count_tags']
)

qs = Book.objects.values('tags').annotate(tags_names=models.F('tags__name'))
print('Query:\n', qs.query)
print('Count books after query =\t', qs.count())


print('Raw query return next count books =\t', len([i for i in Book.objects.raw(""""""
    SELECT
        ""books"".""id"",
        ""books_tags"".""tag_id"",
        ""tags"".""name"" AS ""tags_names""
    FROM ""books""
    LEFT OUTER JOIN
        ""books_tags"" ON (""books"".""id"" = ""books_tags"".""book_id"")
    LEFT OUTER JOIN
        ""tags"" ON (""books_tags"".""tag_id"" = ""tags"".""id"")
    ORDER BY
        ""books"".""date_added"" ASC
    """"""
)]))
}}}


Output

    Real count books =	 5
    Total count tags in all books =	 13
    Query: SELECT ""books_tags"".""tag_id"", ""tags"".""name"" AS ""tags_names"" FROM ""books"" LEFT OUTER JOIN ""books_tags"" ON (""books"".""id"" = ""books_tags"".""book_id"") LEFT OUTER JOIN ""tags"" ON (""books_tags"".""tag_id"" = ""tags"".""id"") ORDER BY ""books"".""date_added"" ASC
    Count books after query =	 13
    Raw query return next count books =	 13


Models:


{{{
class Book(models.Model):
    """"""
    Model for books
    """"""

    id = models.UUIDField(primary_key=True, editable=False, default=uuid.uuid4)
    #other fields
    tags = models.ManyToManyField(
        Tag,
        verbose_name=_('Tags'),
        related_name='books',
    )


class Tag(models.Model):
    """"""
    Model tags for another models
    """"""

    name = models.SlugField(
        _('name'),
        max_length=30,
        unique=True,
        allow_unicode=True,
    )
}}}
"	Bug	closed	Database layer (models, ORM)	1.9	Normal	invalid			Unreviewed	0	0	0	0	0	0