48

I would like to retrieve a bunch of rows from my database using a set of filters.

I was wondering if conditional filter is applicable in django. That is, "filter if variable is not None, or not apply filtering otherwise".

Something like this:

user = User.objects.get(pk=1)
category = Category.objects.get(pk=1)
todays_items = Item.objects.filter(user=user, date=now()).conditional_filter(category=category))

What I would like to do is apply category filter only if category is not None.

If category is None (means it is not given in the request object) then this filter would not be applied at all. This would save me a bunch of 'if-elif-else' situations.

Is there a way to do this?

Improve this question

5 Answers 5

Reset to default
67

You can chain queries:

user = User.objects.get(pk=1)
category = Category.objects.get(pk=1)
qs = Item.objects.filter(user=user, date=now())
if category:
    qs = qs.filter(category=category)

As queryset are executed lazily, DB hit will occur only when you display items.

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

3 Comments

Most Satisfyingly simple answer
This is a readable and easy to implement answer, as well as being performant because querysets are lazily-evaluated.
Do you need to do qs = qs.filter(...) or is it enough just to call filter(...) on an existing QuerySet?
19

They are several approach to your issue. One approach is to play with Complex lookups with Q objects

from django.db.models import Q

user = User.objects.get(pk=1)
category = Category.objects.get(pk=1)

f1 = Q( user=user, date=now() )
f_cat_is_none = Q( category__isnull = True )
f_cat_is_not_none = Q( category=category )

todays_items = Item.objects.filter( f1 & ( f_cat_is_none | f_cat_is_not_none ) )

I don't right understand in your answer if this is the query you are looking for, but, with this example you can compose easily your own query.

Edited due OP comment

category__isnull == True means that, in database, the item has not an associated category. Perhaps the query you are looking for is:

from django.db.models import Q

user_pk = 1
category_pk = 1  #some times None

f = Q( user__pk = user_pk, date=now() )
if category_pk is not None:
  f &= Q( category__pk = category_pk )

todays_items = Item.objects.filter( f  )

This is only a code sample, fit it to your requirements. Be careful with single _ and double __.

Improve this answer

4 Comments

Thanks, what I don't understand is category__isnull = True. None of my objects have NULL in their category field. Which make it to be always False.
@xpanta, explained on answer.
@danihp, sorry I still don't quite understand the __isnull=True. Does it apply to this situation: a form containing "category", the user fills and submits the form.If user does't enter "category" so this is "" for this value. Then filter database WITHOUT the constraint of "category" if I followed your 1st example? Please correct me if my understanding correct. Thanks in advance.
@Elena, sorry, not sure what are you asking for. __isnull on first sample means items without category.
12

Well, this is rather old question but for those who would like to do the conditional filtering on one line, here is my approach (Btw, the following code can probably be written in a more generic way):

from django.db.models import Q

def conditional_category_filter(category):
    if category != None:
        return Q(category=category)
    else:
        return Q() #Dummy filter

user = User.objects.get(pk=1)
category = Category.objects.get(pk=1)
todays_items = Item.objects.filter(conditional_category_filter(category), user=user, date=now())

The only thing you need to watch is to use the conditional_category_filter(category) call before the keyword arguments like user=user. For example the following code would throw an error:

todays_items = Item.objects.filter(user=user, date=now(), conditional_category_filter(category))
Improve this answer

Comments

4

To continue on @iuysal answer:

To make it generic you need to pass the key too as parameter, to do that you need to pass a dictionary, here's how I did it:

Create your dictionary like this: 

filters = {'filter1': 'value1', 'filter2__startswith': 'valu', ...}

Then pass it to your Item filters like this: 

Item.objects.filter(*[Q(**{k: v}) for k, v in filters.items() if v], filter3='value3')

The first version less cryptic version I had:

def _filter(filters):
    filters = []
    for k, v in n.items():
        if v:
            filters.append(Q(**{k: v}))
    return filters

filters = _filter({'name': name})
return Item.objects.filter(*filters)

Unpacking explanation: We want to give Q (queries) as args to objects.filter as args while we want to give kwargs to Q()

I have this on production now (I will just modify the filters names because it's sensitive):

def get_queryset(self):
    filter1 = self.request.GET.get('filter1 ', '')
    filter2__startswith = self.request.GET.get('filter2_prefix ', '')

    def filters_to_Qs(filters):
        return [Q(**{k: v}) for k, v in filters.items() if v]

    filters = {'filter1': filter1 ,
               'filter2__startswith': filter2__startswith }

    return Order.objects.filter(*filters_to_Qs(filters))
Improve this answer

Comments

1 
from django.db.models import Q

qs = Users.objects.filter(
                    p_id=parent_id,
                    status=True
                ).all()

if user_id>0:
    qs = qs.filter( ~Q(id=user_id) )

in qs we will get the filtered results

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.