Django Order by field ignoring NULL

   Order_by is a commonly used django query to order the queryset based on the order_by field name. Basic syntax of order_by is shown below.

MyModel.objects.all().order_by('field_name')

Consider the following models.py

from django.db import models

class SelectedProducts(models.model):
    '''
    Model for storing the product basic updation details.
    '''
    name = models.CharField(max_length=250)
    price = models.FloatField(default=0.0)
    count = models.IntegerField(blank=True, null=True, max_length=5)

  def __unicode__(self):
       return self.name

Now consider the case where you have 3 entries in your table.

Name         Price           Count

abc              15.0               5
lmo            10.25              2
xyz             12.50           (None)

According to the following query:

     SelectedProducts.objects.all().order_by('count')

Output:
[ <objects:xyz>, <objects:lmo>, <objects:abc>]

Since the count of xyz is None, it will be shown first.
Now, to show model instance with count 'None' at the end, you can use following methods:

1. Method: Using 'extra'

Based on this method code will  be:

    selected = SelectedProducts.objects.all().order_by('count')
    q = selected.extra(select={'count_null': 'count is null'})
    q = q.extra(order_by=['count_null'])
    print q

Output:
[ <objects:lmo>, <objects:abc>, <objects:xyz>]

In some cases the above approach returns sql syntax error.

2. Method: Using 'aggregate'

Based on this method code will  be:

    from django.db.models import Count
    q = SelectedProducts.objects.all().annotate(null_count=Count('count')).order_by('-null_count', 'count')
    print q

Output:
[ <objects:lmo>, <objects:abc>, <objects:xyz>]

Django Admin Action

   If you are looking for a way to update for change many objects at once via admin panel then the best way is to use Django's admin action. By setting a custom action you can change the object list in the admin by selecting the objects you want to change and go to the bottom of the page where you can see a drop down. Click on the drop down and it will show a list of admin action including the custom action that you have created. By default you can see 'Delete Selected User' action.

Creating Sample Model

Lets create a sample table in models.py to.

 
from django.db import models

class ProductsUpdated(models.model):
    '''
    Model for storing the product basic updation details.
    '''
    name = models.CharField(max_length=250)
    price = models.FloatField(default=0.0)
    updated = models.BooleanField(default=False)

  def __unicode__(self):
       return self.name

Writing Custom Action

In admin.py

 
from django.contrib import admin

def updated(modeladmin, request, queryset):
    '''
    Custom action method
    '''
    queryset.update(updated=True)           # Updating the 'úpdated' field
updated.short_description="Items Updated"   # Description thats shown in the bottom drop down

Now call the action method in the admin of the table ProductsUpdated.
The whole code would look like this:

from django.contrib import admin
from myapp.models import ProductsUpdated


def updated(modeladmin, request, queryset):
    queryset.update(updated=True)
updated.short_description =  "Items Updated"


def ProductsUpdatedAdmin(admin.ModelAdmin):
    list_display = [ 'title', 'úpdated']
    action = [updated]

admin.site.register(ProductsUpdated, ProductsUpdatedAdmin)

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