Serve datatables with ajax from Django

Datatables is an amazing resource which lets you quickly display lots of data in tables, with sorting, searching and pagination all built in.

The simplest way to use it is to populate the table when you load the page.  Then the sorting, searching and pagination all just happen by themselves.

If you have a lot of data, you can improve page load times by just serving the data you need to, using ajax. On first sight, this is made easy too.  However, be warned: if the server is sending only the data needed, then the server needs to take care of sorting, searching and pagination. You will also need to control the table column sizes more carefully.

There’s quite a lot required to get this right, so I thought I’d share what I’ve learned from doing this in Django.

Start with the following html. This example demonstrates using the render function to insert a link into the table.

</pre>
<div class="row">
<table class="table table-striped table-bordered" id="example" style="clear: both;">
<thead>
<tr>
<th>Name</th>
<th>Value</th>
</tr>
</thead>
</table>
</div>
<pre>

and javascript:

$(document).ready(function() {
    exampleTable = $('#example').dataTable( {
        "aaSorting": [[ 2, "asc" ]],
        "aoColumns": [
            { "mData":"name", "sWidth":"150px" },
            { "mData":"supplier", "sWidth":"150px",
              "mRender": function (supplier, type, full)  {
                             return '<a href="'+supplier.slug+'">' + supplier.name + '</a>';
                         },
            },
            { "sType": 'numeric', "sClass": "right", "mData":"price", "sWidth":"70px" },
        ],
        "bServerSide": true,
        "sAjaxSource": "{% url 'api' 'MyClass' %}",
        "bStateSave" : true, // optional
                fnStateSave :function(settings,data){
                        localStorage.setItem("exampleState", JSON.stringify(data));
                },
                fnStateLoad: function(settings) {
                        return JSON.parse(localStorage.getItem("exampleState"));
                },
        fnInitComplete: function() { // use this if you don't hardcode column widths
            this.fnAdjustColumnSizing();
        }
    });
    $('#example').click(function() { // only if you don't hardcode column widths
        exampleTable.fnAdjustColumnSizing();
    });

Next you need to write an API for the data. I’ve put my api in its own file, apis.py, and made it a generic class-based view, so I’ve added to urls.py:

from django.conf.urls import patterns, url
from myapp import views, apis

urlpatterns = patterns('',
   ...
   url(r'^api/v1/(?P<cls_name>[\w-]+)/$',apis.MyAPI.as_view(),name='api'),
)

Then in apis.py, I put the following. You could use Django REST framework or TastyPie for a fuller solution, but this is often sufficient. I’ve written it in a way that can work across many classes; just pass the class name in the URL (with the right capitalization). One missing feature here is an ability to sort on multiple columns.

import sys
import json

from django.http import HttpResponse
from django.views.generic import TemplateView
from django.core.serializers.json import DjangoJSONEncoder

import myapp.models

class JSONResponse(HttpResponse):
    """
    Return a JSON serialized HTTP response
    """
    def __init__(self, request, data, status=200):
        # pass DjangoJSONEncoder to handle Decimal fields
        json_data = json.dumps(data, cls=DjangoJSONEncoder)
        super(JSONResponse, self).__init__(
            content=json_data,
            content_type='application/json',
            status=status,
        )

class JSONViewMixin(object):
    """
    Return JSON data. Add to a class-based view.
    """
    def json_response(self, data, status=200):
        return JSONResponse(self.request, data, status=status)

# API

# define a map from json column name to model field name
# this would be better placed in the model
col_name_map = {'name': 'name',
                'supplier': 'supplier__name', # can do foreign key look ups
                'price': 'price',
               }
class MyAPI(JSONViewMixin, View):
    "Return the JSON representation of the objects"
    def get(self, request, *args, **kwargs):
        class_name = kwargs.get('cls_name')
        params = request.GET
        # make this api general enough to handle different classes
        klass = getattr(sys.modules['myapp.models'], class_name)

        # TODO: this only pays attention to the first sorting column
        sort_col_num = params.get('iSortCol_0', 0)
        # default to value column
        sort_col_name = params.get('mDataProp_{0}'.format(sort_col_num), 'value')
        search_text = params.get('sSearch', '').lower()
        sort_dir = params.get('sSortDir_0', 'asc')
        start_num = int(params.get('iDisplayStart', 0))
        num = int(params.get('iDisplayLength', 25))
        obj_list = klass.objects.all()
        sort_dir_prefix = (sort_dir=='desc' and '-' or '')
        if sort_col_name in col_name_map:
            sort_col = col_name_map[sort_col_name]
            obj_list = obj_list.order_by('{0}{1}'.format(sort_dir_prefix, sort_col))

        filtered_obj_list = obj_list
        if search_text:
            filtered_obj_list = obj_list.filter_on_search(search_text)

        d = {"iTotalRecords": obj_list.count(),                # num records before applying any filters
            "iTotalDisplayRecords": filtered_obj_list.count(), # num records after applying filters
            "sEcho":params.get('sEcho',1),                     # unaltered from query
            "aaData": [obj.as_dict() for obj in filtered_obj_list[start_num:(start_num+num)]] # the data
        }

        return self.json_response(d)

This API depends on the model for two extra things:

  • the object manager needs a filter_on_search method, and
  • the model needs an as_dict method.

The filter_on_search method is tricky to get right. You need to search with OR on the different fields of the model, and AND on different words in the search text. Here is an example which subclasses the QuerySet and object Manager classes to allow chaining of methods (along the lines of this StackOverflow answer).

from django.db import models
from django.db.models import Q
from django.db.models.query import QuerySet

class Supplier(models.Model):
    name = models.CharField(max_length=60)
    slug = models.SlugField(max_length=200)

class MyClass(models.Model):
    name = models.CharField(max_length=60)
    supplier = models.ForeignKey(Supplier)
    price = models.DecimalField(max_digits=8, decimal_places=2)
    objects = MyClassManager()

    def as_dict(self):
        """
        Create data for datatables ajax call.
        """
        return {'name': self.name,
                'supplier': {'name': self.supplier.name, 'slug': self.supplier.slug},
                'price': self.price,
                }

class MyClassMixin(object):
    """
    This will be subclassed by both the Object Manager and the QuerySet.
    By doing it this way, you can chain these functions, along with filter().
    (A simpler approach would define these in MyClassManager(models.Manager),
        but won't let you chain them, as the result of each is a QuerySet, not a Manager.)
    """
    def q_for_search_word(self, word):
        """
        Given a word from the search text, return the Q object which you can filter on,
        to show only objects containing this word.
        Extend this in subclasses to include class-specific fields, if needed.
        """
        return Q(name__icontains=word) | Q(supplier__name__icontains=word)

    def q_for_search(self, search):
        """
        Given the text from the search box, search on each word in this text.
        Return a Q object which you can filter on, to show only those objects with _all_ the words present.
        Do not expect to override/extend this in subclasses.
        """
        q = Q()
        if search:
            searches = search.split()
            for word in searches:
                q = q & self.q_for_search_word(word)
        return q

    def filter_on_search(self, search):
        """
        Return the objects containing the search terms.
        Do not expect to override/extend this in subclasses.
        """
        return self.filter(self.q_for_search(search))

class MyClassQuerySet(QuerySet, MyClassMixin):
    pass

class MyClassManager(models.Manager, MyClassMixin):
    def get_query_set(self):
        return MyClassQuerySet(self.model, using=self._db)

This is a stripped down version of my production code. I haven’t fully tested this stripped down version, so please let me know if you find any problems with it.

Hope it helps!

  

One thought on “Serve datatables with ajax from Django”

  1. Hi. This is great.

    Can you code about a fully crud applications with datables and django. I wil be really greatfull.

    Thanks for yout help. Have a nice day.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>