Filter on multiple fields with OR condition

[mohammadhasanzadeh]

Hi,
How possible to filter on multiple fields with OR condition!?
For example:
first_name = foo OR last_name = bar
Equivalent to:
model.objects.filter(Q(first_name=foo) | Q(last_name=bar))

[luzfcb]

Thanks for opening this, this is exactly the same question I have.

Currently, I am getting the following warning:

/usr/local/lib/python3.7/site-packages/django_filters/rest_framework/backends.py:128: 
UserWarning: <class 'project.api.views.FooBarViewSet'> is not compatible with schema generation

And I have a FilterSet and ViewSet that looks like this:

# models
class FooBarUserAssignment(models.Model):
    user = models.ForeignKey("auth.User", on_delete=models.CASCADE)
    foobar = models.ForeignKey("FooBar", on_delete=models.CASCADE)

    class Meta:
        unique_together = (
            ("user", "foobar"),
        )


class FooBarGroupAssignment(models.Model):
    group = models.ForeignKey("auth.Group", on_delete=models.CASCADE)
    foobar = models.ForeignKey("FooBar", on_delete=models.CASCADE)

    class Meta:
        unique_together = (
            ("group", "foobar"),
        )
        
class FooBar(models.Model):
    title = models.CharField(max_length=160, unique=True)

    users = models.ManyToManyField(
        to="auth.User",
        through=FooBarUserAssignment,
    )
    groups = models.ManyToManyField(
        to="auth.Group",
        through=FooBarGroupAssignment,
    )

    def __str__(self):
        return self.title

# filters
from django_filters import rest_framework as rest_framework_filters

class FooBarFilter(rest_framework_filters.FilterSet):
    title = rest_framework_filters.CharFilter(field_name="title", lookup_expr="icontains")

    class Meta:
        model = FooBar
        fields = ("title", )

# viewsets
class FooBarViewSet(ModelViewSet):
    queryset = Foobar.objects.order_by("-title")
    serializer_class = FooBarSerializer
    filterset_class = FooBarFilter
   
    def get_queryset(self):
        queryset = self.queryset

        q_name = Q()
        rel_name = self.request.query_params.get("rel_name", None)
        if rel_name:
            q_name = Q(users__name=rel_name)

        q_groups = Q()
        rel_groups = self.request.query_params.get("rel_groups", "").split(",")
        if any(rel_groups):
            q_groups = Q(groups__name__in=rel_groups)

        qs = queryset.filter(q_name | q_groups).distinct()
        return qs

How could I build a FilterSet to execute the exact same query?
I was able to implement part of FilterSet, but still do not know what the best way to move OR logic from ViewSet.get_queryset() to FilterSet

import django_filters
from django_filters import rest_framework as rest_framework_filters

class CharInFilter(django_filters.BaseInFilter, rest_framework_filters.CharFilter):
    pass

class FooBarFilter(rest_framework_filters.FilterSet):
    title = rest_framework_filters.CharFilter(field_name="title", lookup_expr="icontains")
    rel_name = rest_framework_filters.CharFilter(field_name="users__name", lookup_expr="exact")
    rel_groups = CharInFilter(field_name="groups__name")

    class Meta:
        model = FooBar
        fields = ("title", )

    def filter_queryset(self, queryset):
        qs = super().filter_queryset(queryset)
        return qs.distinct()

[guzzijones]

It looks like this is part of this extension of django-filter that can be installed along side django-filter. https://github.com/philipn/django-rest-framework-filters#complex-operations

[rpkilby]

Hi all. I've created #1167, which should provide group-level validation and filtering. For the OP, usage would look something like:

class UserFilter(FilterSet):
    class Meta:
        model = User
        field = ['username', 'first_name', 'last_name']
        groups = [
            CombinedGroup(filters=['first_name', 'last_name'], combine=operator.or_),
        ]

Any feedback on the PR would be greatly appreciated.

[JeromeK13]

Still the same issue here, are their any solutions yet?

[minaee]

@JeromeK13 looks like nothing yer! I have same question

[mohammadhasanzadeh]

I've done something like the following, I'm not sure works for all cases but solved my problem:

class BaseFilter(django_filters.FilterSet):
    def OR(self, queryset, field_name, value):
        if not hasattr(self, "groups"):
            setattr(self, "groups", {})

        self.groups[field_name] = value
        return queryset

    @property
    def qs(self):
        base_queryset = super().qs

        if not hasattr(self, "groups"):
            return base_queryset

        query = Q()
        for key, value in self.groups.items():
            query |= Q(**{key: value})

        return base_queryset.filter(query)

class PlanFilter(BaseFilter):
    double_visit__or = django_filters.UUIDFilter("double_visit", method="OR")
    visitor__or = django_filters.UUIDFilter("visitor", method="OR")
    class Meta:
        model = models.Plan
        fields = {
            "id": ["exact"],
            "date": ["exact", "gte", "lte",],
            "visitor": ["exact"],
            "doctor": ["exact"],
            "double_visit": ["exact"]
        }

[minaee]

I believe I found a much much simpler way:

from django.db.models.query_utils import Q
user_contacts = Contact.objects.order_by('-contact_date').filter(Q(sender_id=request.user.user_id)|Q(receiver_id=request.user.user_id)) 

In my case, I wanted to gather all the rows that where have spesific sender_id or receiver_id. And, I wanted them to be in one query set to order them by date

[anandmate]

Hi,
How possible to filter on multiple fields with OR condition!?
For example:
first_name = foo OR last_name = bar
Equivalent to:
model.objects.filter(Q(first_name=foo) | Q(last_name=bar))

Import Q

django.db.models import Q

It worked for me.

[lpdswing]

I've done something like the following, I'm not sure works for all cases but solved my problem:

class BaseFilter(django_filters.FilterSet):
    def OR(self, queryset, field_name, value):
        if not hasattr(self, "groups"):
            setattr(self, "groups", {})

        self.groups[field_name] = value
        return queryset

    @property
    def qs(self):
        base_queryset = super().qs

        if not hasattr(self, "groups"):
            return base_queryset

        query = Q()
        for key, value in self.groups.items():
            query |= Q(**{key: value})

        return base_queryset.filter(query)

class PlanFilter(BaseFilter):
    double_visit__or = django_filters.UUIDFilter("double_visit", method="OR")
    visitor__or = django_filters.UUIDFilter("visitor", method="OR")
    class Meta:
        model = models.Plan
        fields = {
            "id": ["exact"],
            "date": ["exact", "gte", "lte",],
            "visitor": ["exact"],
            "doctor": ["exact"],
            "double_visit": ["exact"]
        }

it worked for me too.

[abidibo]

This #1134 (comment) worked for me too.
I currently use a filter class with the addition of the lookup concept:

class FilterSetWithOr(dffilters.FilterSet):
    OR_LOOKUPS = {}

    def OR(self, queryset, field_name, value):
        if not hasattr(self, "groups"):
            setattr(self, "groups", {})

        if field_name in self.OR_LOOKUPS:
            self.groups['%s__%s' % (field_name, self.OR_LOOKUPS.get(field_name))] = value
        else:
            self.groups[field_name] = value

        return queryset

    @property
    def qs(self):
        base_queryset = super().qs

        if not hasattr(self, "groups"):
            return base_queryset

        query = models.Q()
        for key, value in self.groups.items():
            query |= models.Q(**{key: value})

        return base_queryset.filter(query)

class UserFilter(FilterSetWithOr):
    OR_LOOKUPS = {
        'username': 'icontains',
        'profile__last_name': 'icontains',
    }
    username__icontains__or = filters.CharFilter("username", method="OR")
    profile__last_name__icontains__or = filters.CharFilter("profile__last_name", method="OR")
    class Meta:
        model = User
        fields = {
            'groups__id': ['exact', 'in'],
        }

[codewithkushagra]

You can do one thing that is call for two query set separately and then chain them
EX-
from itertools import chain

q1=QMODEL.Course.objects.all().filter(student_id=id1)
q1=QMODEL.Course.objects.all().filter(student_id=id2)
courses=chain(q2,q1)

[TDM-Altipeak]

Into FilterSet class, add:

`
@classmethod
def get_groups(cls):
return [("field_name_1", "field_name_2")]

@property
def groups(self):
    return self.__class__.get_groups()

def filter_queryset(self, queryset):
    for name, value in self.form.cleaned_data.items():

        is_grouped = False
        for group in self.groups:
            if name.split("__")[0] in group:
                is_grouped = True

                if value is None:
                    continue

                q_filter = Q()
                for group_field_name in group:
                    for filter_name in self.filters.keys():
                        if filter_name.startswith(group_field_name):
                            q_filter |= Q(**{filter_name: value})
                queryset = queryset.filter(q_filter)

        if not is_grouped:
            queryset = self.filters[name].filter(queryset, value)

    return queryset`

Additionally, can add into init method:

self.filters["field_name_2"].field.required = False self.filters["field_name_2"].field.widget = HiddenInput() self.filters["field_name_2"].field.widget.attrs['readonly'] = 'readonly'
for don't display the respective field

[smohsenmohseni]

CombinedGroup

Hi, i can't import CombinedGroup from django-filter, there is problem? i use django-filter==22.1

[reddytocode]

groups = [
            CombinedGroup(filters=['first_name', 'last_name'], combine=operator.or_),
        ]

@mohsnMohsni That is tied to his PR, is not part of django_filters

[Abdullah0297445]

@mohammadhasanzadeh I will advise you to use https://github.com/cloudblue/django-rql, it supports all kinds of complex operations which currently aren't supported in django-filter.

[osumoclement]

Here's what I came up with to solve this, not ideal as I'd have liked user__groups__id__in and user__departments__id__in to remain in the fields attribute with all the other fields, but it is what it is. Works like a charm, I can filter by users who are in a list of groups OR departments, AND must have attributes for the other fields IF they are in the URL query string.

class ActivityFilter(OrganisationalFilter):
    class Meta:
        model = Activity
        fields = dict(
            {
                "task__project__id": [
                    "in",
                ],
                "id_from_client": [
                    "exact",
                ],
                "activity_type": [
                    "in",
                ],
                "time": ["gte", "lte"],
            },
            **OrganisationalFilter.Meta.fields,
        )

    def filter_queryset(self, queryset):
        interim_qs = queryset.filter(
            (
                Q(user__id__in=self.data["user__id__in"].split(","))
                if "user__id__in" in self.data
                else Q(user__isnull=False)
            )
            | (
                Q(user__groups__id__in=self.data["user__groups__id__in"].split(","))
                if "user__groups__id__in" in self.data
                else Q(user__isnull=False)
            )
            | (
                Q(
                    user__departments__id__in=self.data[
                        "user__departments__id__in"
                    ].split(",")
                )
                if "user__departments__id__in" in self.data
                else Q(user__isnull=False)
            )
        )

        qs = super().filter_queryset(interim_qs)

        return qs