Opened 4 years ago

Closed 4 years ago

Last modified 4 years ago

#27144 closed Bug (invalid)

annotating across a ManyToManyField returns duplicate results

Reported by: Seti Owned by: nobody
Component: Database layer (models, ORM) Version: 1.9
Severity: Normal Keywords:
Cc: Triage Stage: Unreviewed
Has patch: no Needs documentation: no
Needs tests: no Patch needs improvement: no
Easy pickings: no UI/UX: no

Description

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,
    )

Change History (4)

comment:1 Changed 4 years ago by Seti

Summary: It is may be bug the Django ORM, may be I bad know SQL may it badly read the Django docs and made googling.It is may be bug the Django ORM, may be I bad know SQL may I badly read the Django docs and made googling.

comment:2 Changed 4 years ago by Shai Berger

Resolution: invalid
Status: newclosed

Hi,

We value your interest in the Django project. However, you are asking questions in the wrong place and in the wrong way.

Please don't open an issue unless you are fairly convinced that you found a problem with Django.

With respect to how to ask questions -- it is not about the English language, it is about the content of the tickets you open. Please see e.g. How to ask smart questions -- the link points directly to the list of translations, so you can hopefully read it in your language.

For the specific matter you raised here -- yes, I believe this is the result one should expect. It is not a bug. For what you are trying to achieve, try to use prefetch_related.

comment:3 Changed 4 years ago by Tim Graham

Component: UncategorizedDatabase layer (models, ORM)
Summary: It is may be bug the Django ORM, may be I bad know SQL may I badly read the Django docs and made googling.annotating across a ManyToManyField returns duplicate results
Type: UncategorizedBug

#10060 might be relevant.

comment:4 in reply to:  2 Changed 4 years ago by Seti

Replying to shaib:

Hi,

We value your interest in the Django project. However, you are asking questions in the wrong place and in the wrong way.

Please don't open an issue unless you are fairly convinced that you found a problem with Django.

With respect to how to ask questions -- it is not about the English language, it is about the content of the tickets you open. Please see e.g. How to ask smart questions -- the link points directly to the list of translations, so you can hopefully read it in your language.

For the specific matter you raised here -- yes, I believe this is the result one should expect. It is not a bug. For what you are trying to achieve, try to use prefetch_related.

Please, delete this ticket

Note: See TracTickets for help on using tickets.
Back to Top