# ORM -- ManyToMany, annotate, distinct: is this a bug ?

**URL:** <https://forum.djangoproject.com/t/orm-manytomany-annotate-distinct-is-this-a-bug/8736>\
**Category:** Using Django\
**Created:** [July 11, 2021, 12:34pm UTC](https://forum.djangoproject.com/t/orm-manytomany-annotate-distinct-is-this-a-bug/8736 "2021-07-11T12:34:13Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![RK38](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/rk38/32/5120_2.png) [@RK38](https://forum.djangoproject.com/u/RK38)\
**Post date:** [July 11, 2021, 12:34pm UTC](https://forum.djangoproject.com/t/orm-manytomany-annotate-distinct-is-this-a-bug/8736/1 "2021-07-11T12:34:14Z")

</div>

Hi,

I’m not sure that this is a bug, and I would like to have confirmation before I open a ticket. Suppose I have the following models:

```auto
class Auteur(models.Model):
    nom=models.CharField(max_length=20)

class Genre(models.Model):
    nom=models.CharField(max_length=20)

class Book(models.Model):
    auteurs=models.ManyToManyField(Auteur)
    genre=models.ManyToManyField(Genre)

```

So, Book model has two ManyToMany fields. Let’s create some data:

```auto
paul=Auteur(nom="Paul")
paul.save()
robert=Auteur(nom="Robert")
robert.save()
vincent=Auteur(nom="Vincent")
vincent.save()

roman=Genre(nom="Roman")
roman.save()
nouvelle=Genre(nom="Nouvelle")
nouvelle.save()
action=Genre(nom="Action")
action.save()

b1=Book()
b1.save()
b1.auteurs.set([paul, vincent])
b1.genre.set([roman, action])

b2=Book()
b2.save()
b2.auteurs.set([paul, robert])
b2.genre.set([nouvelle, action])

```

Now, if I want a Book queryset with annotation of the number of authors, I can do:

```auto
books=Book.objects.annotate(NumAuteurs=Count('auteurs'))

```

It works fine:

```auto
>>> books[0].NumAuteurs
2

```

But suppose I have to filter Book queryset like this:

```auto
books=Book.objects.filter(genre__in=[action, roman])

```

Of course, the first book appears twice:

```auto
>>> books
<QuerySet [<Book: Book object (1)>, <Book: Book object (1)>, <Book: Book object (2)>]>

```

So, it’s convenient to use distinct:

```auto
books=Book.objects.filter(genre__in=[action, roman])

```

Now, I have the two books, one time each:

```auto
>>> books
<QuerySet [<Book: Book object (1)>, <Book: Book object (2)>]>

```

Now, I use this queryset to get the number of authors, like before:

```auto
books=books.annotate(NumAuteurs=Count('auteurs'))

```

I would expect a correct number of authors. But:

```auto
>>> books[0].NumAuteurs
4

```

And more surprisingly, result is not correct even if one annotates before filtering:

```auto
books=Book.objects.annotate(NumAuteurs=Count('auteurs'))
>>> books[0].NumAuteurs
2
>>> books=books.filter(genre__in=[action, roman]).distinct()
>>> books[0].NumAuteurs
4

```

So, is this the expecting behavior ?

---

<div class="post-metadata">

**Author:** ![KenWhitesell](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/kenwhitesell/32/280_2.png) [@KenWhitesell](https://forum.djangoproject.com/u/KenWhitesell)\
**Post date:** [July 11, 2021, 1:56pm UTC](https://forum.djangoproject.com/t/orm-manytomany-annotate-distinct-is-this-a-bug/8736/2 "2021-07-11T13:56:50Z")

</div>

When in doubt, check the SQL being generated.  
e.g. `print(books.query)`

You’ll probably find that you are getting a cartesian join between the two many-to-many relationships and that this would be the expected behavior in that case.

---

<div class="post-metadata">

**Author:** ![RK38](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/rk38/32/5120_2.png) [@RK38](https://forum.djangoproject.com/u/RK38)\
**Post date:** [July 11, 2021, 4:11pm UTC](https://forum.djangoproject.com/t/orm-manytomany-annotate-distinct-is-this-a-bug/8736/3 "2021-07-11T16:11:01Z")

</div>

I checked it. One get

```auto
SELECT DISTINCT "base_book"."id", COUNT("base_book_auteurs"."auteur_id") AS "NumAuteurs"
FROM "base_book" LEFT OUTER JOIN "base_book_auteurs" ON ("base_book"."id" = "base_book_auteurs"."book_id")
			INNER JOIN "base_book_genre" ON ("base_book"."id" = "base_book_genre"."book_id")
			WHERE "base_book_genre"."genre_id" IN (3, 1) GROUP BY "base_book"."id";

```

But I was expecting something like:

```auto
SELECT "b_id", COUNT("base_book_auteurs"."auteur_id") AS N
FROM (
	SELECT DISTINCT "base_book"."id" AS "b_id"
	FROM "base_book" INNER JOIN "base_book_genre" ON ("b_id" = "base_book_genre"."book_id")
	WHERE "base_book_genre"."genre_id" IN (3,1)
	)
LEFT OUTER JOIN "base_book_auteurs" ON ("b_id" = "base_book_auteurs"."book_id")
GROUP BY "b_id";

```