Netbox: Exclusion/negate filter on API

[Yannis100]

I checked the documentation and few links but it looks like there is no support for exclusion filtering?

Environment

• Python version: 3.6.9
• NetBox version: 2.5.13

Proposed Functionality

Supports exclusion filtering on the API : eg. /?status!=1

https://stackoverflow.com/questions/23208169/negation-or-exclude-filter-in-django-rest-framework
https://docs.djangoproject.com/en/dev/ref/models/querysets/#exclude

Use Case

Filtering for easier external reports, in my case getting all devices that are not in status: active

Database Changes

None I think

External Dependencies

[jeremystretch]

This is only feasible if we can devise a way to dynamically establish inverse filters for each existing filter without duplicating the existing filters.

[spolack]

I would really love that feature. Currently i'm scraping all the interfaces and filter locally in order to retrieve all LAG members.

Using "/?status!=1" style would be nice, altough pynetbox doesnt seem to have support for this yet. Please also consider adding a magic keyword to the term. Like "/?status=!1" or "/?status!=not 1".

[hSaria]

I've experimented with this for a bit and here are my findings:

• Applying this globally can be done by creating a class of DjangoFilterBackend and overriding its filter_queryset. Example at the end on how I've achieved this. Changing the filterset_base on this class will not work as it is being overridden by the individual views.
• Applying this per-filterset is possible by inheriting from a class (e.g. NegateFilterSet) for which the filter_queryset method is adjusted (similar to the global one).
• After digging into django-rest-framework-filters, I've noticed that it will not work on multivalue fields (i.e. region=europe&region!=germany will negate both europe and germany). Even then, it would still require a change on each filterset such that it inherits from FilterSet of that module (they're using filterset_base on the filter backend but, as explained in the first note, this won't work for us as we override the class an a per-view basis).

Regarding global backend vs per-filterset inheritance, if we have filtersets that shouldn't have negation, it might be better to go with the latter to have more control on which filtersets support it. If all of the filtersets are safe to exclude, then the former is just fine.

Here's the example code for applying negation/exclusion globally (per-view is very similar). You'd throw this is netbox/api.py

from django_filters.rest_framework.backends import DjangoFilterBackend

class APIFilterBackend(DjangoFilterBackend):
    def filter_queryset(self, request, queryset, view):
        qs = super().filter_queryset(request, queryset, view)

        excludes = {param[:-1]: value for param, value in request.query_params.lists() if param[-1] == '!'}
        filterset_class = self.get_filterset_class(view, qs)

        if excludes and filterset_class:
            # Update the data with excludes (other fields are kept in case they are needed in form clean)
            data = request.query_params.copy()
            for name, value in excludes.items():
                data.setlist(name, value)

            filterset = filterset_class(data=data, queryset=qs)

            # Remove redundant filters (i.e. already filtered in super)
            for name in [key for key in filterset.filters if key not in excludes]:
                filterset.filters.pop(name)

            # Invert the filters (the remaining filters are for the excluded fields)
            for name in filterset.filters:
                filterset.filters[name].exclude = not filterset.filters[name].exclude

            qs = filterset.qs

        return qs

and then update netbox/settings.py with:

     'DEFAULT_FILTER_BACKENDS': (
-        'django_filters.rest_framework.DjangoFilterBackend',
+        'netbox.api.APIFilterBackend',
     ),

The above also handles TreeNodeMultipleChoiceFilter. For example, if you had

Regions: EU
         FI, with parent EU
         UK, with parent EU

Sites: eu1 in region EU
       fi1 in region FI
       uk1 in region UK

you can apply filters like /api/dcim/sites/?region=eu&region!=uk which would return sites in EU, but excluding those in UK, or eu1 and fi1.

[lampwins]

I am closing this as it has been implemented as a part of #4121 in the 4121-filter-lookup-expressions branch.