Is there a best practice to create a Django query with extensible filters?

Question

Suppose I have a "find invoice page" where users can input (or not) different fields in order to search for invoices to display them in a list. Suppose this list of fields might grow over time with additional db fields. My current (working) solution is the following but I'm wondering if there is a better way to allow possible additional fields and adapt the query accordingly.

query.py [custom query composer]

from .models import Invoice

class FindQuery:

    def __init__(self):
        self.filters = {}
        self.fq_sql = ''
        self.fq_select = 'SELECT * '
        self.fq_from = 'FROM invoice_invoice '
        self.fq_where = 'WHERE 1=1 '
        self.fq_order = ' ORDER BY iv_created_at DESC'

    def set_filters(self, user, year, client):
        if user:
            self.filters['request_user'] = user

        if year:
            self.filters['selected_year'] = year

        if client:
            self.filters['selected_client'] = client

        # adding here new fields
    
    def apply_filter(self):

        if 'request_user' in self.filters:
            self.fq_where += ' AND iv_created_by = ' + str(self.filters.get('request_user'))

        if 'selected_year' in self.filters:
            self.fq_where += ' AND iv_year = ' + str(self.filters.get('selected_year'))
            
        if 'selected_client' in self.filters:
            self.fq_where += ' AND iv_client = ' + str(self.filters.get('selected_client'))

        # adding here new fields

        self.fq_sql = self.fq_select + self.fq_from + self.fq_where + self.fq_order
        return Invoice.objects.raw(self.fq_sql)

views.py (Django Rest Framework)

from .query import FindQuery

class InvoiceViewSet(viewsets.ModelViewSet):
    serializer_class = InvoiceSerializer


    def get_queryset(self):
        q = FindQuery()

        user = self.request.user.id
        year = self.request.GET.get('selected_year')
        client = self.request.GET.get('selected_client')
        # adding here new fields

        q.set_filters(user, year, client)
        qs = q.apply_filter()
        result = Invoice.objects.filter(iv_id__in=[q.iv_id for q in qs])
        return result

Typically the ORM is used. A `ModelViewSet` allows to define a list of filters that each time add a new filter to the queryset and eventually thus retrieve the filtered records. — Willem Van Onsem, Mar 10 '21 at 17:17
Why are you writing the query like that? Firstly the ORM can easily do what you want. Secondly you are wide open to SQL Injection!!! — Abdul Aziz Barkat, Mar 10 '21 at 17:17
Check [API Guide on Filtering - DRF Docs](https://www.django-rest-framework.org/api-guide/filtering/#api-guide) — Abdul Aziz Barkat, Mar 10 '21 at 17:35
https://www.django-rest-framework.org/api-guide/filtering/#custom-generic-filtering suggest to override get_queryset() in view with a filter for owner. Basically it is the same as my user filter and could be used as Invoice.objects.filter(iv_creared_by=self.request.user). But how can you add many non mandatory filters inside .filter()? I solved this adding AND statements in the raw SQL based on presence in the request. How would you do that in ORM? — rpalloni, Mar 10 '21 at 20:40
Maybe, after composing the dictionary of filters with those in the request it could be passed to filter like Invoice.objects.filter(**filters) where filters={key:value} of actually used filters — rpalloni, Mar 10 '21 at 21:27

score 0 · Answer 1 · answered Mar 11 '21 at 12:00

Here the solution with ORM approach. Short and fast! Also solves the problem of additional/optional search fields.

class InvoiceViewSet(viewsets.ModelViewSet):
    serializer_class = InvoiceSerializer

    def get_queryset(self):
        
        # input value from search page
        user = self.request.user.id
        year = self.request.GET.get('selected_year')
        client = self.request.GET.get('selected_client')

        filters = {
            # model name: input value
        }

        if user:
            filters['iv_created_by'] = user

        if year:
            filters['iv_year'] = year

        if client:
            filters['iv_client'] = client

        result = Invoice.objects.filter(**filters)

        return result

Is there a best practice to create a Django query with extensible filters?

1 Answers1