Add support for optimized join paths in FilterSet

[saichander17]

Currently when using multiple filter methods in a FilterSet that share common join paths, Django generates duplicate joins in the final query. This leads to less efficient queries and potential performance issues.

Example:

class MyFilterSet(FilterSet):
    def status_filter(self, queryset, name, value):
        return queryset.filter(items__details__status=True)
        
    def category_filter(self, queryset, name, value):
        return queryset.filter(items__details__categories__id__in=[1,2])

When both filters are applied, the generated query contains duplicate joins:

SELECT DISTINCT ... FROM "parent" 
INNER JOIN "items" ON ("parent"."id" = "items"."parent_id") 
INNER JOIN "details" ON ("items"."id" = "details"."item_id") 
INNER JOIN "items" T4 ON ("parent"."id" = T4."parent_id") 
INNER JOIN "details" T5 ON (T4."id" = T5."item_id") 
WHERE ("details"."status" = true AND "categories"."id" IN (1, 2))

Maybe, Is it possible to create a new thing similar to filterset which has a behaviour where each filters returns Q() and the filter_queryset method collects all Q() and applies it to the queryset!

If there's already been any discussion on this or something similar which shows how to handle such scenarios, please do point me towards it.

Currently, I'm thinking of creating a class inherited from filterset and modifying the init and filter_queryset methods to achieve this result.

[saichander17]

Hi,
Can someone please help here?

[carltongibson]

You need to be creating a (multi-)filter that takes the (e.g. two) parameters and applies them in a single step.

See https://docs.djangoproject.com/en/5.1/topics/db/queries/#spanning-multi-valued-relationships

[carltongibson]

There's an example for this pattern you can see here: https://gitlab.com/-/snippets/2237049

[carltongibson]

Duplicate of #1020.