Opened 7 years ago

Closed 7 years ago

Last modified 7 years ago

#28921 closed Uncategorized (invalid)

On filtering nested many to many fields

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

Description

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

  1. 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)

Change History (4)

comment:1 by Simon Charette, 7 years ago

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=...)

comment:2 by Sassan Haradji, 7 years ago

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

comment:3 by Sassan Haradji, 7 years ago

Resolution: fixed
Status: newclosed

comment:4 by Ramiro Morales, 7 years ago

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