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

**URL:** <https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533>\
**Category:** Using the ORM\
**Created:** [September 5, 2024, 8:58pm UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533 "2024-09-05T20:58:57Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![DoOrQuit](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doorquit/32/16890_2.png) [@DoOrQuit](https://forum.djangoproject.com/u/DoOrQuit)\
**Post date:** [September 5, 2024, 8:58pm UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/1 "2024-09-05T20:58:57Z")

</div>

Hi everyone. Second day in a row I’m struggling trying to filter instance by M2M field to match exactly another model M2M field contains similar data.  
Let’s say I have models:

```auto
class Options(models.Model):
    name = model.CharField() # opt1, opt2, opt3 ... opt5

class Foo(models.Model):
    options = models.ManyToMany(Options)
    size = models.IntgerField()

class Bar(models.Model):
    options = models.ManyToMany(Options)
    size = models.IntgerField()

```

So, now I need `filter()` `Foo` instance to get all objects which contain exactly the same `Option` that `Bar` instance has.  
For example: If `Bar` has options [‘opt2’, ‘opt3’, ‘opt5’] I want to get all `Foo` instance which have exactly **3** options above [‘opt2’, ‘opt3’, ‘opt5’]. Not one of them, not two of them.  
I managed to get close with code:

```auto
bars = Bar.objects.all()
for bar in bars:
    bar_options = [option for option in bar.options.all()]

    foo_match = Foo.objects.filter(size = bar.size)
    if bar_options:
        foo_match = foo_match.filter(options__in=bar_options)\
                               .annotate(num_options=Count("options"))\
                               .filter(num_options=len(bar_options))

```

In most of the cases it works however in some iterations of `Bar` I have matched the same `Foo` instances even they have more options then `Bar` has.  
Would appreciate any help.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [September 5, 2024, 10:18pm UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/2 "2024-09-05T22:18:43Z")

</div>

Hello @DoOrQuit, could you share the database backend you are using?

This is not easy to achieve but depending on the backend we might be able to take shorcuts.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [September 5, 2024, 10:52pm UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/3 "2024-09-05T22:52:50Z")

</div>

On Postgres the following should work in a constant number of queries (2).

```sql
# Keep a reference to foo and bar options with outer references so they can be reused.
foo_options = Foo.options.through.objects.filter(
    foo_id=OuterRef("pk")
).values("option_id")
bar_options = Bar.options.through.objects.filter(
    bar_id=OuterRef(OuterRef("pk"))
).values("option_id")
bars = Bar.objects.annotate(
    # Accumulate all the foo ids
    matching_options_foo_pks=ArraySubquery(
        Foo.objects.filter(
            # All the foo with no set difference between foo and bar options.
            ~Exists(
                foo_options.difference(bar_options)
            ),
            # ... and not difference between bar and foo options (both directions)
            ~Exists(
                bar_options.difference(foo_options)
            ),
        ).values("pk")
    )
)
# Retrieve all matching foos across all bars in a single query a bit
# like prefetch related does.
matching_foos = Foo.objects.in_bulk(
    set(itertools.chain.from_iterable(
        bar.matching_options_foo_pks for bar in bars
    ))
)
for bar in bars:
    foo_matches = [
        matching_foos[foo_pk]
        for foo_pk in bar.matching_options_foo_pks
    ]

```

This should generate SQL of the form

```sql
SELECT
    bar.*,
   ARRAY(
       SELECT foo.id
       FROM foo
       WHERE (
           NOT EXISTS (
               SELECT option_id FROM foo_options WHERE foo_options.foo_id = foo.id
               EXCEPT
               SELECT option_id FROM bar_options WHERE bar_options.bar_id = bar.id
           )
           AND NOT EXISTS (
               SELECT option_id FROM bar_options WHERE bar_options.bar_id = bar.id
               EXCEPT
               SELECT option_id FROM foo_options WHERE foo_options.foo_id = foo.id
           )
       )
   ) matching_options_foo_pks
FROM bar 

```

---

<div class="post-metadata">

**Author:** ![DoOrQuit](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doorquit/32/16890_2.png) [@DoOrQuit](https://forum.djangoproject.com/u/DoOrQuit)\
**Post date:** [September 6, 2024, 8:37am UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/5 "2024-09-06T08:37:40Z")

</div>

Thank you for a response. Your guess regarding DB backend was right.  
However I’m experiencing some exceptions in code :

```auto
matching_foos = Foo.objects.in_bulk(
    set(itertools.chain.from_iterable(
        bar.matching_options_foo_pks for bar in bars
    ))
)

```

The SQL Exceptions:

```auto
django.db.utils.ProgrammingError: invalid reference to FROM-clause entry for table "app_foo"
LINE 1: ...app_foo_options" U0 WHERE U0."foo_id" = ("app...
                                                             ^
HINT: Perhaps you meant to reference the table alias "v0".

```

Probably some naming issue but cannot resolve it yet.

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [September 6, 2024, 1:53pm UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/6 "2024-09-06T13:53:37Z")

</div>

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
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 🙂

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

```python
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
   )

```

---

<div class="post-metadata">

**Author:** ![charettes](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/charettes/32/27_2.png) [@charettes](https://forum.djangoproject.com/u/charettes)\
**Post date:** [September 9, 2024, 1:32am UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/7 "2024-09-09T01:32:55Z")

</div>

The actual [ticket](https://code.djangoproject.com/ticket/35744) and [associated PR](https://github.com/django/django/pull/18555).

---

<div class="post-metadata">

**Author:** ![DoOrQuit](https://sea2.discourse-cdn.com/flex026/user_avatar/forum.djangoproject.com/doorquit/32/16890_2.png) [@DoOrQuit](https://forum.djangoproject.com/u/DoOrQuit)\
**Post date:** [September 19, 2024, 8:06am UTC](https://forum.djangoproject.com/t/filtering-model-instances-m2m-field-to-exactly-match-another-m2m-field-or-list/34533/8 "2024-09-19T08:06:09Z")

</div>

@charettes thank you for such a detailed answer which gave me some ideas.  
I ended up with something like this:

```auto
bars = Bar.objects.all()
for bar in bars:
    bar_options = [option for option in bar.options.all()]

    foo_base_qs = Foo.objects.filter(size = bar.size)
    if bar_options:
        options_num = len(bar_options)
        foo_match = foo_base_qs.annotate(
            total_options=Count("options__name"),
            matching_options=Count(
                'options__name',
                filter=Q(options __name__ in=options)
            )
        ).filter(
            matching_options=options_num,
            total_options=options_num
            )
        return foo_match

```

Probably not perfect for some cases but works so far in my particular cases.