Search code examples
pythondjangodjango-orm

Make inner joins by using Q objects in Django

I'm customizing a model's Admin change list page in my project.

This model contains a Many to Many field and I want to allow multi-selection filtering.

I've used the Django Admin Multi Select Filter package to do this but I noticed that the default behaviour in Django's __in lookup is to be inclusive and not exclusive.

Imagine I have an object A associated with M2M objects 1 and 2 and object B associated with M2M objects 2 and 3. If I make the following query Model.objects.filter(m2m_model__in=[1,2]) I will have both objects A and B being retrieved, since the search will use the following condition:

Index Cond: (models_model_m2mfield.model_id = ANY ('{1,2}'::bigint[]))

I was able to customize the behaviour of the search by doing the following:

            for choice in choices:
                queryset = queryset.filter(**{self.lookup_kwarg: choice})

This generates inner joins and returns the value I'm waiting for (only objects matching every option).

INNER JOIN "models_model_m2mfield" ON ("models_m2mmodel"."id" = "models_model_m2mfield"."m2mfield_id") INNER JOIN "models_model_m2mfield" T4 ON ("models_m2mmodel"."id" = T4."m2mfield_id") WHERE ("models_model_m2mfield"."model_id" = 4 AND T4."model_id" = 5)

I've tried using Q objects to get the same result, but I couldn't make it work. Is it possible to achieve the same behaviour using the Q class?

Solution

  • Is it possible to achieve the same behaviour using the Q class?

    No, you can not do this with a single Q object.

    If you use two (or more) .filter(…) clauses [Django-doc] that JOIN on the same model, it will result in separate LEFT OUTER JOINs on the same table. So that is why this will work. This is what we need, but it can, especially if not optimized quite well, in a large query that is not very effective.

    What we can do however, is work with an .alias(…) [Django-doc] and a .filter(…) to check if the number of matches is the required number so:

    from django.db.models import Count

data = [1, 2]
Model.objects.alias(
    nmatch=Count('m2m_model', filter=Q(m2m_model__in=data), distinct=True)
).filter(nmatch=len(set(data)))

    We thus first count the number of matches, and then we restrict that number to the number of unique ones in data.

    As for the MultiSelectFieldListFilter, likely that still can be achieved, since such filters have a method to filter the queryset, so no Q object is necessary: we can use arbitrary code to filter the queryset properly. Indeed, see for example this implementation for a SimpleListFilter [Django-doc]