Filtering model instance's m2m field to exactly match another m2m field or list

Using Django Using the ORM
6

So it seems to be a bug in the ORM when subqueries and composed queries are used together, the query works with the following patch to Django

diff --git a/django/db/models/sql/query.py b/django/db/models/sql/query.py
index b33652dac5..34f9322d02 100644
--- a/django/db/models/sql/query.py
+++ b/django/db/models/sql/query.py
@@ -989,6 +989,17 @@ def change_aliases(self, change_map):
                 if alias == old_alias:
                     table_aliases[pos] = new_alias
                     break
+
+        # 3. Rename the external aliases of combined queries.
+        for combined_query in self.combined_queries:
+            external_change_map = {
+                alias: aliased
+                for alias, aliased in change_map.items()
+                if alias in combined_query.external_aliases
+            }
+            if external_change_map:
+                combined_query.change_aliases(external_change_map)
+
         self.external_aliases = {
             # Table is aliased or it's being changed and thus is aliased.
             change_map.get(alias, alias): (aliased or alias in change_map)

I’ll file a ticket later today about it and thanks to your example I already have a test handy :slight_smile:

As for making it work, albeit less efficiently, until this gets resolved in a released version of Django the following should do

bars = Bar.objects.prefetch_related("options")  # two queries
for bar in bars:
   bar_option_ids = sorted([option.id for option in bar.options])
   foos = Foo.objects.alias(  # one query per, effectively a N+1
       options_ids=ArraySubquery(
           Foo.options.through.objects.filter(
               foo_id=OuterRef("pk")
           ).order_by("option_id").values("option_id")
       ),
   ).filter(
       options_ids=bar_option_ids
   )