Django Complex Query

    Recently I got a chance to work on database query handling in a project. There were lot of objects filtering using field names, some advanced filtering etc. There I met a simple piece of code that was doing something amazing, our Django's Q object. Normally when we use a get or filter on object we specify some parameters inside the filter or get or ... So what this Q object does is it gives you the power to add logical operation to our filtering query. We can do our logical AND or OR operation in Q object. I'll list the syntax of Q object operation one by one.

First of all you need to import the Q object. (obviously...)

from django.db.models import Q 

Consider our models.py as

class Author(models.Model):
    name = models.CharField(max_length=200)
    
    def __unicode__(self):
        return self.name


class Store(models.Model):
    name = models.CharField(max_length=200)
    
    def __unicode__(self):
        return self.name


class Books(models.Model):
    author_name = models.ForeignKey(Author)
    name = models.CharField(max_length=200)
    store_name = models.ForeignKey(Store)

    def __unicode__(self):
        return self.name

Now our logical operations:

AND Operation:

    Search for book written by specific author and in specific store.

Books.objects.filter(Q(author_name__contains='John Doe') & Q(store_name__contains='amazon'))

OR Operation:

Search for book written by specific author or books in specific store.

Books.objects.filter(Q(author_name__contains='John Doe') | Q(store_name__contains='amazon'))

NOT Operation:

Search for book not in specific store.

Books.objects.filter(~Q(store_name__contains='amazon'))

Now suppose you have a list of store names and authors you need to query and its not good to use above approach since length will be high and makes things more complex. For these kinds of situation you can use the following approach:

To check books present in all store. (AND Operation)

my_stores = [list of store names]
q_object = Q()
for store in my_stores:
    q_object.add(Q(store_name__contains=store), Q.AND)
Books.objects.filter(q_object)

To check books present in any one of the store. (OR Operation)

my_stores = [list of store names]
q_object = Q()
for store in my_stores:
    q_object.add(Q(store_name__contains=store), Q.OR)
Books.objects.filter(q_object)

Note: By default Q object operation will be AND