In this tutorial we are going to build API as a data source for a DataTable jQuery plugin.
Introduction¶
There is this awesome plugin I have used recently for displaying and sorting data https://datatables.net
It is very easy to start using.
You just make an HTML table, add a loop in Django template to display contents of table and then initialize this table with a JavaScript call.
<table id="myDataTable">
... table contents
</table>
<script>
$(document).ready( function () {
$('#myDataTable').DataTable();
} );
</script>
Sounds fun and easy until you need to display a lot of data.
At the point where there are at least a thousand rows the size of the page will be huge, it will take a very long time and resources (CPU/RAM) to generate that page. Also page load time will be long killing user experience.
Thankfully, there is a solution to this problem.
ServerSide mode for DataTable.
From DataTables documentation:
Server-side processing
There are times when reading data from the DOM is simply too slow or unwieldy, particularly when dealing with many thousands or millions of data rows. To address this DataTables' server-side processing feature provides a method to let all the "heavy lifting" be done by a database engine on the server-side (they are after all highly optimised for exactly this use case!), and then have that information drawn in the user's web-browser. Consequently, you can display tables consisting of millions of rows with ease.
https://datatables.net/manual/server-side
In this article I want to show how to build the Django API with Django REST Framework that can be used as source for Datatables.
For this tutorial I will make a new repository from our Djangitos template and cloning it to my machine.
Go to Djangitos GitHub repository https://github.com/appliku/djangitos
Click button "Use this template"
Give a name to the new repository and click the "Create repository from template" button.
When new repository is ready, copy the path and use it to clone repo on your machine with git clone, in this case git clone git@github.com:appliku/tutorial_jquery_datatable_api.git
Switch to directory of the project with cd tutorial_jquery_datatable_api
Create an .env file with the following contents:
DATABASE_URL=postgresql://djangito:djangito@db/djangito
REDIS_URL=redis://redis/0
DJANGO_SECRET_KEY=123
DJANGO_DEBUG=True
It is needed in order to run our project with docker-compose.
Now you can open your editor or IDE, for pycharm on mac you can type open -a pycharm .
Now let's create an django application where we will put models, views and templates for this tutorial.
docker-compose run web python manage.py startapp datatable
This will create a directory in the root of our project datatable
Let's add the app to INSTALLED_APPS setting, so Django recognizes it.
Open djangito/settings.py and add 'datatable' to PROJECT_APPS.
Create models¶
I want to make this tutorial complex enough so there is chance to illustrate where can be performance issues and how to solve them and generally have a chance to talk about adjacent topics.
As an example we'll use an imaginary service company that does certain work for clients.
They need to track statuses of their work orders, what should be done and who are their clients.
Open datatable/models.py. Put these models in this file.
from django.db import models
from datatable.tuples import ORDER_STATUSES
class Client(models.Model):
name = models.CharField(max_length=255)
phone = models.CharField(max_length=255)
email = models.EmailField()
def __str__(self):
return self.name
class Meta:
verbose_name = "Client"
verbose_name_plural = "Clients"
ordering = ('name',)
class Order(models.Model):
STATUS_CHOICES = (
(ORDER_STATUSES.proposal, 'Proposal'),
(ORDER_STATUSES.in_progress, 'In Progress'),
(ORDER_STATUSES.done, 'Done'),
(ORDER_STATUSES.rejected, 'Rejected'),
)
name = models.CharField(max_length=255)
client = models.ForeignKey(Client, on_delete=models.CASCADE)
address = models.CharField(max_length=255)
state = models.CharField(max_length=255)
zip_code = models.CharField(max_length=10)
status = models.IntegerField(choices=STATUS_CHOICES, default=ORDER_STATUSES.proposal)
date_start = models.DateField()
date_end = models.DateField()
def __str__(self):
return self.name
class Meta:
verbose_name = "Order"
verbose_name_plural = "Orders"
ordering = ('date_end',)
class OrderLine(models.Model):
order = models.ForeignKey(Order, on_delete=models.CASCADE)
name = models.CharField(max_length=255, )
description = models.TextField()
unit_price = models.DecimalField(max_digits=10, decimal_places=2)
quantity = models.IntegerField()
def __str__(self):
return self.name
class Meta:
verbose_name = "Order Line"
verbose_name_plural = "Order Lines"
ordering = ('name',)
Edit datatable/admin.py to register our models in Django Admin:
from django.contrib import admin
from datatable.models import Order, OrderLine, Client
admin.site.register(Order)
admin.site.register(OrderLine)
admin.site.register(Client)
For statuses we'll use namedtuple.
Create a file datatable/tuples.py with this code:
from collections import namedtuple
ORDER_STATUSES = namedtuple('ORDER_STATUSES', 'proposal in_progress done rejected')._make(range(4))
Namedtuples are great for preventing errors and also provides code completion in IDE.
Now let's make migrations for these models. Run this command in the root of your project.
docker-compose run web python manage.py makemigrations
That's the output you should expect:
Now let's apply migrations, in order to do that, run the migrate management command:
docker-compose run web python manage.py migrate
Also we need a superuser. Let's create one.
Appliku Djangitos template comes with a simplified way to create superuser, the management command called makesuperuser.
docker-compose run web python manage.py makesuperuser
It will generate a super user with email/username admin@example.com and a random password.
Find the password in the output of this command, we'll need it in a few moments.
src/tutorial_jquery_datatable_api % docker-compose run web python manage.py makesuperuser
Creating tutorial_jquery_datatable_api_web_run ... done
Using selector: EpollSelector
admin user not found, creating one
===================================
A superuser was created with email admin@example.com and password NDTbnmPuyieX
===================================
admin@example.com
src/tutorial_jquery_datatable_api %
Let's start our project with this command:
docker-compose up
When you see this, then our app is running.
web_1 | Watching for file changes with StatReloader
web_1 | Watching for file changes with StatReloader
web_1 | Performing system checks...
web_1 |
web_1 | System check identified no issues (0 silenced).
web_1 | April 30, 2021 - 07:27:51
web_1 | Django version 3.1.6, using settings 'djangito.settings'
web_1 | Starting development server at http://0.0.0.0:8060/
web_1 | Quit the server with CONTROL-C.
Open the app in your browser at http://0.0.0.0:8060/admin/ and log in with admin@example.com and the password that was generated for you my makesuperuser command.
On the admin dashboard you can find our models.
You can go crazy now and create dozen orders with multiple line items so we have data to work with in the next steps.
I recommend creating several different clients so we can test sorting and search features of datatable.
Datatable with server rendered table¶
For the purpose of illustration of what Datatable can do and comparison later let's first create a page where datatable works with server rendered table.
Create a directory and a file datatable/templates/base.html where we include all the common structure and resources for our views.
<html lang="en">
<head>
<title>DataTable</title>
<link href="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/css/bootstrap.min.css" rel="stylesheet"
integrity="sha384-eOJMYsd53ii+scO/bJGFsiCZc+5NDVN2yr8+0RDqr0Ql0h+rP48ckxlpbzKgwra6" crossorigin="anonymous">
<link rel="stylesheet" href="//cdn.datatables.net/1.10.24/css/jquery.dataTables.min.css">
{% block extra_head %}
{% endblock %}
</head>
<body>
<div class="container mt-5">
{% block content %}
{% endblock %}
</div>
<script src="https://code.jquery.com/jquery-3.6.0.min.js"
integrity="sha256-/xUj+3OJU5yExlq6GSYGSHk7tPXikynS7ogEvDej/m4=" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/bootstrap@5.0.0-beta3/dist/js/bootstrap.bundle.min.js"
integrity="sha384-JEW9xMcG8R+pH31jmWH6WWP0WintQrMb4s7ZOdauHnUtxwoG2vI5DkLtS3qm9Ekf"
crossorigin="anonymous"></script>
<script src="//cdn.datatables.net/1.10.24/js/jquery.dataTables.min.js"></script>
{% block extra_js %}
{% endblock %}
</body>
</html>
Now let's make a template for our first view with static datatable. Let's call it datatable_static.html and full path will be datatable/template/datatable_static.html
{% extends "datatable/base.html" %}
{% block content %}
<table id="myStaticDatatable">
<thead>
<tr>
<th>ID</th>
<th>ORDER</th>
<th>CLIENT</th>
<th>TOTAL</th>
<th>STATUS</th>
</tr>
</thead>
<tbody>
{% for order in order_list %}
<tr>
<td>{{ order.id }}</td>
<td>
{{ order.name }}
<br>
{{ order.address }} {{ order.state }} {{ order.zip_code }}
</td>
<td>
{{ order.client.name }}
<br>{{ order.client.phone }}
{{ order.client.email }}
</td>
<td>{{ order.amount }}</td>
<td>{{ order.get_status_display }}</td>
</tr>
{% endfor %}
</tbody>
</table>
{% endblock %}
{% block extra_js %}
<script>
$(document).ready(function () {
$('#myStaticDatatable').DataTable();
});
</script>
{% endblock %}
Open the file datatable/views.py, let's create our first view here.
from django.db.models import Sum, F, DecimalField
from django.shortcuts import render
from datatable.models import Order
def datatable_static(request, *args, **kwargs):
orders_qs = Order.objects.all().select_related('client').annotate(
amount=Sum(
F('orderline__unit_price') * F('orderline__quantity'),
output_field=DecimalField())
)
return render(
request=request,
template_name="datatable/datatable_static.html",
context={
"order_list": orders_qs
})
Create datatable/urls.py file:
from django.urls import path
from datatable.views import datatable_static
urlpatterns = [
path('static', datatable_static, name='datatable_static'),
]
Edit project's urls.py: djangito/urls.py. Add a line to include our datatable urls.
path('datatable/', include('datatable.urls')),
Now if we open our page at http://0.0.0.0:8060/datatable/static we'll see our table:
Let's summarise points you should pay attention to:
- We made a base template that includes all resources and for our view we made template that extends the base one
- We used
.annotate()to calculate total amount of order on the database level. If we'd do it on python level it would require fetching all OrderLines and calculating them and it will be a massive performance hit. - Finally, we made an HTML table in our template, filled it with out data using for-loop and made it a datatable.
Now let's make it not static, but server-rendered via API.
Django REST Framework API for Datatable¶
To make our API we need another View, a line in urls.py and a serializer.
Create datatable/serializers.py.
We will create only one serializer, because we only need a flat object to display in datatable. We could use nested objects with datatable too, but I see no reason to make our code more complex.
from rest_framework import serializers
from datatable.models import Order
class OrderSerializer(serializers.ModelSerializer):
amount = serializers.DecimalField(max_digits=10, decimal_places=2)
client_name = serializers.ReadOnlyField(source='client.name')
client_email = serializers.ReadOnlyField(source='client.email')
client_phone = serializers.ReadOnlyField(source='client.phone')
status = serializers.SerializerMethodField()
class Meta:
model = Order
fields = (
'id', 'name', 'address',
'state', 'zip_code', 'status',
'date_start', 'date_end',
'client_name', 'client_phone', 'client_email', 'amount')
def get_status(self, obj: Order):
return obj.get_status_display()
Now add a new class based view to our datatable/views.py
class DataTableAPIView(ListAPIView):
serializer_class = OrderSerializer
def get_queryset(self):
return Order.objects.all().select_related('client').annotate(
amount=Sum(
F('orderline__unit_price') * F('orderline__quantity'),
output_field=DecimalField())
)
def filter_for_datatable(self, queryset):
# filtering
search_query = self.request.query_params.get('search[value]')
if search_query:
queryset = queryset.annotate(
search=SearchVector(
'name',
'client__name',
'address', 'zip_code')
).filter(search=search_query)
# ordering
ordering_column = self.request.query_params.get('order[0][column]')
ordering_direction = self.request.query_params.get('order[0][dir]')
ordering = None
if ordering_column == '0':
ordering = 'id'
if ordering_column == '1':
ordering = 'name'
if ordering and ordering_direction == 'desc':
ordering = f"-{ordering}"
if ordering:
queryset = queryset.order_by(ordering)
return queryset
def list(self, request, *args, **kwargs):
draw = request.query_params.get('draw')
queryset = self.filter_queryset(self.get_queryset())
recordsTotal = queryset.count()
filtered_queryset = self.filter_for_datatable(queryset)
try:
start = int(request.query_params.get('start'))
except ValueError:
start = 0
try:
length = int(request.query_params.get('length'))
except ValueError:
length = 10
end = length + start
serializer = self.get_serializer(filtered_queryset[start:end], many=True)
response = {
'draw': draw,
'recordsTotal': recordsTotal,
'recordsFiltered': filtered_queryset.count(),
'data': serializer.data
}
return Response(response)
Add 2 more items to datatable/urls.py:
path('dynamic', TemplateView.as_view(template_name='datatable/datatable_dynamic.html'), name='datatable_dynamic'),
path('data', DataTableAPIView.as_view(), name='datatable_data'),
dynamic refers to a generic TemplateView and data refers to our class based view.
Add the template for our dynamic table, datatable/templates/datatable/datatable_dynamic.html:
{% extends "datatable/base.html" %}
{% block content %}
<table id="myStaticDatatable">
<thead>
<tr>
<th>ID</th>
<th>ORDER</th>
<th>CLIENT</th>
<th>TOTAL</th>
<th>STATUS</th>
</tr>
</thead>
<tbody>
</tbody>
</table>
{% endblock %}
{% block extra_js %}
<script>
let data_url = '{% url "datatable_data" %}';
$(document).ready(function () {
$('#myStaticDatatable').DataTable({
'order': [[1, 'desc']],
'processing': false,
'serverSide': true,
'ajax': {
url: data_url,
dataSrc: 'data'
},
columns: [
{
data: 'id',
orderable: true
},
{
data: null,
render: function (data, type, row) {
return `${row.name}<br>${row.address} ${row.state} ${row.zip_code}`;
},
orderable: true
},
{
data:null,
render: function (data, type, row){
return `${row.client_name}<br/>${row.client_phone}<br/>${row.client_email}`
},
orderable: false
},
{
data: 'amount',
orderable: false
},
{
data: 'status',
orderable: false
}
]
});
});
</script>
{% endblock %}
Change from the static table template is that we removed data for-loop, added an URL to our API data_url and initialized the table with more configuration options.
Let's go over initialization of the datatable:
orderis default ordering for the table, it will be the second column, descending order.processingis disabled, I didn't want the "Processing" label to appear while table is loading. It just looks ugly.serverSideis what makes datatable rely on server to load results according to sorting, filtering, pageajaxis an object that tells where our API resides.ajax.urlis the API endpoint URL anddatais object in endpoint response JSON that contains actual datacolumnsdefines how to display data in columns from the JSON endpoint returns. Thedataattribute tells to use a field from JSON for response.renderis a function to render the column cell and we use it to build a piece of HTML based on several fields of our JSON,datashould benullin this case.orderablewhen enabled allows user to sort by this column.
Go to the page http://0.0.0.0:8060/datatable/dynamic and see the table that works the same way as before, but it sources data from API.
Full source of the project for this article can be found here: https://github.com/appliku/tutorial_jquery_datatable_api