Compiling django filters into a variable and executing it at runtime?

Question

I'm trying to write a search function for my model. A customer has the following fields:

cstid = models.AutoField(primary_key=True, unique=True)
name = models.CharField(max_length=35)
age=models.IntegerField()
gender = models.CharField(max_length=10, default='')
mobile = models.CharField(max_length=15, default='')
email = models.CharField(max_length=50, default='')
address = models.CharField(max_length=80, default='')
city = models.CharField(max_length=25, default='')

My html form will submit the following data by POST via an ajax call to django:

var data = {
    "name": name,
    "age": age,
    "email": email,
    "address": address,
    "phone": phone,
    "city": city
};
data = $(this).serialize() + "&" + $.param(data);

These will be verified in the django view, and a search query performed, as below:

def searchpat_for_docwise_appt (request):
    from django.core.exceptions import ObjectDoesNotExist
    from django.db.models import Q
    from django.db.models import CharField
    from django.db.models.functions import Lower
    CharField.register_lookup(Lower, "lower")
    if request.method == 'POST':
        name = request.POST.get('name')
        age = request.POST.get('age')
        # gender = request.POST.get('gender')
        phone = request.POST.get('phone')
        email = request.POST.get('email').lower()
        address = request.POST.get('address')
        city = request.POST.get('city')
        if age.isdigit():            
            ANDSearchResult = customer.objects.filter(name__lower__contains=name.lower(), age=age, mobile__contains=phone, email__lower__contains=email.lower(
        ), address__lower__contains=address.lower(), city__lower__contains=city.lower())
        else:
            ANDSearchResult = customer.objects.filter(name__lower__contains=name.lower(), mobile__contains=phone, email__lower__contains=email.lower(
            ), address__lower__contains=address.lower(), city__lower__contains=city.lower())
        if len(ANDSearchResult) < 1:
            errmsg = 'No search results for AND search'
            print("Error message is <%s>" % errmsg)
        else:
            print ("ANDSearchResult is <%s>" % ANDSearchResult)
        errmsg = ''

        if age.isdigit():
            ORSearchResult = customer.objects.filter(
            Q(name__lower__contains=name.lower()) | Q(age=age) | Q(
                mobile__contains=phone) | Q(email__lower__contains=email.lower()) | Q(address__lower__contains=address.lower()) | Q(city__lower__contains=city.lower()))
        else:
            ORSearchResult = customer.objects.filter(
                Q(name__lower__contains=name.lower()) | Q(
                    mobile__contains=phone) | Q(email__lower__contains=email.lower()) | Q(address__lower__contains=address.lower()) | Q(city__lower__contains=city.lower()))
        if len(ORSearchResult) < 1:
            errmsg = errmsg + 'No search results for OR search'
            print("Error message is <%s>" % errmsg)
        else:
            print ("ORSearchResult is <%s>" % ORSearchResult)
        print(errmsg)
        return HttpResponse(errmsg)
    else:
        errmsg = 'No correct POST request. No valid response.'
        print("Error message is <%s>" % errmsg)
        return HttpResponse(errmsg)

As you can note above, I am doing two different queries simultaneously. I want an AND operator like query where search has to satisfy all input parameters. I also need an OR operator which should provide a result if any of the fields matches the entered string for the corresponding field. By this I mean that if a patient has a name Jeff with age 56, and a patient Kane with age 23; and I enter jef in the name field and 23 in the age field, both should be listed.

The problem arises when there are empty strings in the search boxes. An OR search with an empty string gives all objects as results. Obviously I dont want to return a patient record just because my search strings are null.

Other than the long winded solution of checking for all combinations of search queries like this:

if (name != '' and age != '' and phone != '' and email != '' and address != '' and city != '':
    ORSearchResult = customer.objects.filter(Q(name__lower__contains=name.lower()) | Q(age=age) | Q(mobile__contains=phone) | Q(email__lower__contains=email.lower()) | Q(address__lower__contains=address.lower()) | Q(city__lower__contains=city.lower()))
elif (age != '' and phone != '' and email != '' and address != '' and city != '':
    ORSearchResult = customer.objects.filter(Q(age=age) | Q(mobile__contains=phone) | Q(email__lower__contains=email.lower()) | Q(address__lower__contains=address.lower()) | Q(city__lower__contains=city.lower()))        
elif (phone != '' and email != '' and address != '' and city != '':
    ORSearchResult = customer.objects.filter(Q(mobile__contains=phone) | Q(email__lower__contains=email.lower()) | Q(address__lower__contains=address.lower()) | Q(city__lower__contains=city.lower()))            

.... Other permutations ...

What is a better solution? An obvious idea that comes to mind is, to try to compile these filters into a variable and pass it to the filter function. Am I on the right track? How can I do this?

score 1 · Accepted Answer · answered Aug 15 '18 at 14:32

1

If I understand it correctly, you want to write OR-logic between different conditions, given the corresponding values are not empty (truthiness False). We can construct such a Q-object constructor with:

from django.db.models import Q
from functools import reduce
from operator import or_

def or_q_if_truthfull(**kwargs):
    filtered = [Q(**{k: v}) for k, v in kwargs.items() if v]
    if filtered:
        return reduce(or_,filtered)
    else:
        return Q()

Then we can construct the desired Q object with:

my_q = or_q_if_truthfull(
    name__lower__contains=name.lower(),
    age=age,
    mobile__contains=phone,
    email__lower__contains=email.lower()
    address__lower__contains=address.lower(),
    city__lower__contains=city.lower(),
)

So we can filter with:

ORSearchResult = customer.objects.filter(my_q)

Note: instead of using field__lower__contains=value.lower(), one can use field__icontains=value, which performs a case-invariant filtering, so a more safe and elegant way to construct my_q is:

my_q = or_q_if_truthfull(
    name__icontains=name,
    age=age,
    mobile__contains=phone,
    email__icontains=email
    address__icontains=address,
    city__icontains=city,
)

answered Aug 15 '18 at 14:32

Willem Van Onsem

443,496
30
428
555

I didnt use icontains because of a problem with case insensitive searches and mysql: https://stackoverflow.com/questions/51789688/case-insensitive-search-in-django – Joel G Mathew Aug 15 '18 at 14:35
Can you please explain the language constructs in **kwargs, **{k: v}, or_ etc? My initiation to python has been all of one month. – Joel G Mathew Aug 15 '18 at 14:36
@Droidzone: yes, but personally I think this is "tackling the wrong problem". Usually it is better to first fix the collation. Actually using `foo.lower() == bar.lower()` is *not* case insenstive comparison, since some characters have no lowercase invariant. – Willem Van Onsem Aug 15 '18 at 14:36
1

@Droidzone: `**kwargs` takes all *named* parameters, and construct these into a dictionary. So if you call `or_q_if_truthfull(foo=bar)`, then `kwargs` is `{'foo': bar}`, calling with `**` performs the opposite. – Willem Van Onsem Aug 15 '18 at 14:37
1

@Droidzone: see for example here: https://www.saltycrane.com/blog/2008/01/how-to-use-args-and-kwargs-in-python/ – Willem Van Onsem Aug 15 '18 at 14:37

Compiling django filters into a variable and executing it at runtime?

1 Answers1