For filtering data in Django, build dynamic query for multiple columns

Question

I have to filter data from model based on the run time values. I am getting 5 values via query string. My querystring is like below:

http://127.0.0.1:8000/personal/search/?month=&year=&account=&deliveryManagedFrom=&marketmName=

So, I want to include all or none of the values in the filter so that it displays the desired result. Below is the filter query which I am writing:

sum_tt_count = NetworkRelatedInformation.objects.filter(month=month, year=year, account_id=account, account__deliveryManagedFrom=deliveryManagedFrom, account__marketName=market).aggregate(Sum('tt_count'))
totalttcount = sum_tt_count['tt_count__sum']

It is working well in case, all the values have been provided.

In case, if any value is blank, it should not consider that value and display output as per other filter criteria.

Pls suggest how to implement an OR filter with 5 data inputs. It is not necessary that all 5 data inputs have values . So the value can be None or the value in the querystring

Daniel Roseman · Accepted Answer · 2018-07-08 08:54:57Z

3

Filter the request for non-empty values and then use dictionary expansion to do the query.

q =  {k:v for k, v in request.GET.items() if v}
sum_tt_count = NetworkRelatedInformation.objects.filter(**q).aggregate(Sum('tt_count'))

answered Jul 8, 2018 at 8:54

Daniel Roseman

602k68 gold badges911 silver badges924 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Sachin Jakhar Over a year ago

Thank you so much! It is awesome and generic which can be used for any number of values in the querystring.

Sachin Jakhar Over a year ago

HI Daniel... Can you Pls look on my other question stackoverflow.com/questions/51202612/… I tried multiple ways using filter method, @ property and qs but nothing is working for me.. Pls see if you can support on this

Satendra · Accepted Answer · 2018-07-08 08:39:30Z

2

You can do it using Q object

from django.db.models import Q

NetworkRelatedInformation.objects.filter(Q(month__isnull=True) | Q(month=month), Q(year__isnull=True) | Q(year=year)).aggregate(Sum('tt_count'))

edited Jul 8, 2018 at 8:39

answered Jul 8, 2018 at 8:29

Satendra

6,8554 gold badges29 silver badges48 bronze badges

Comments

Sachin Jakhar · Accepted Answer · 2018-07-08 09:16:13Z

0

For handling the None values i have to explicitly write the below code.

account = request.GET.get('account')
if account is '':
    account = None
month = request.GET.get('month')
if month is '':
    month = None
year = request.GET.get('year')
if year is '':
    year = None


sum_alarm_count = NetworkRelatedInformation.objects.filter(Q(month=month) | Q(year=year) | Q(account_id=account)) \
    .aggregate(Sum('alarm_count'))
totalalarmcount = sum_alarm_count['alarm_count__sum']

answered Jul 8, 2018 at 9:16

Sachin Jakhar

339 bronze badges

Collectives™ on Stack Overflow

For filtering data in Django, build dynamic query for multiple columns

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related