On filtering nested many to many fields

[Sassan Haradji]

Consider that related is a many to many field of Object.

If you filter Object.objects.filter(related__a=1, related__b=2) a=1 and b=2 conditions apply to the same related but if you filter Object.objects.filter(related__a=1).exclude(related__b=2) it returns Object instances that have a related with a = 1 and they have no related that has b = 2. It doesn't provide a way to filter Object instances that have a related with a = 1 which at the same time has b != 2.

Providing __not... for each lookup is one way to solve it. But it still keeps the functionality limited for many to many fields. If user needs all Object instances which have

1. An related object that has a=1 and b=2

and

2. Another or same related object that has b=3 and c!=4

it can't be done with above.
To achieve this we need a way to categorize many to many field object and apply conditions to different categories.
One way to do so is to write it like this:

Object.objects.filter(related__1__a=1, related__1__b=2, related__2__b=3).exclude(related__2__c=4)

The role is to consider the optional number after each many to many field in queries, a category for that many to many field. It is to categorize conditions.

I doubt if filter method in Django has any idea about state of exclude or vice versa so I think implementing above in Django is hard. And making filter and exclude to relate is not intuitive too. Another solution is a combination of above solutions (__not... lookups and lookup categories):

Object.objects.filter(related__1__a=1, related__1__b=2, related__2__b=3, related__2__c__neq=4)

[Simon Charette]

The usual way of mixing inclusion and exclusion logic targeting the same relationship is to use Q objects.

Is there a reason you can't use filter(Q(related__a=1) & ~Q(related__b=2)) to express your filtering logic?

Also, you might be interested in the newly introduced ​filtered relations which should adhere to your re-targeting expectations.

Object.objects.annotate(
    related_1=FilteredRelation('related', Q(related_a=1)),
    related_2=FilteredRelation('related', Q(related_b=2)),
).filter(related_1_b=..., ...).exclude(related_1=..., related_2=...)

[Sassan Haradji]

FilteredRelation is more than appropiate for my use-cases. Thanks. I think we can close it.