Filtering QuerySets dynamically in Django

Introduction

To build a list page that allows filtering and pagination, you have to get a few separate things to work together. Django’s object-relational mapper (ORM) and built-in pagination class make it easy for developers to become productive without the knowledge of how to deal with databases and SQL under the hood. In this guide, you will learn how to filter a QuerySet dynamically using AJAX.

For the example in this article, I have taken a dataset of the top 50 songs on Spotify by country here. You can download the same dataset from here as well. As usual, the code used in this guide is available on GitHub. You can find the link at the end of this guide.

Getting started

To begin, start a new Django project like so:

django-admin startproject my_proj

Then, create a sample app:

cd my_proj
python manage.py startapp my_app

Update the settings.py:

INSTALLED_APPS += [
    'my_app'
]

Here is the directory structure you will be following in the guide:

├── db.sqlite3
├── manage.py
├── my_app/
│   ├── __init__.py
│   ├── admin.py
│   ├── apps.py
│   ├── migrations/
│   ├── models.py
│   ├── templates/
│   │   ├── base.html
│   │   └── index.html
│   ├── tests.py
│   └── views.py
├── my_proj/
│   ├── __init__.py
│   ├── asgi.py
│   ├── settings.py
│   ├── urls.py
│   └── wsgi.py
└── top50contry.csv
└── requirements.txt

Data preparation

Before jumping to the actual code, we first need to push all the data to the database.

I have created a basic model named TopSongPoularity to store the necessary information from the dataset.

Here is the models.py of my_app:

## my_app/models.py

from django.db import models

class TopSongPoularity(models.Model):
    title = models.CharField(max_length = 220)
    artist = models.CharField(max_length = 220)
    top_genre = models.CharField(max_length = 220)
    year = models.IntegerField()
    pop = models.IntegerField()
    duration = models.IntegerField()
    country = models.CharField(max_length = 100)

    def __str__(self):
        return self.title

Now that you have created the model, migrate this to the database with the following:

python manage.py makemigrations
python manage.py migrate

Next, we have to push all of the CSV data to the database, so we will use the shell to execute a script:

python manage.py shell

Run the below script in the shell to push the CSV data to the database:

#Django Shell
import csv
from datetime import datetime

from my_app.models import TopSongPoularity

with open('top50contry.csv', 'r') as fin:
    reader = csv.reader(fin)
    headers = next(reader, None)
    for row in reader:
        obj = {
            "title": row[1],
            "artist": row[2],
            "top_genre": row[3],
            "year": int(row[4]),
            "pop": int(row[15]),
            "duration": int(row[12]),
            "country": row[16]
        }
        TopSongPoularity.objects.create(**obj)

Creating views

Next, let’s write the views. ListTopSongs is a class-based view (CBV) that inherits the View class. In the get() method of the class, it takes up the query parameters and filters the QuerySet accordingly. After the QuerySet gets filtered, it then calls get_paginated_context() to get the paginated data in serialized format.

getCountries() is a function-based view (FBV) which returns the JSON output for all the unique countries in the database:

#my_app/views.py
import json

from django.core.paginator import Paginator
from django.core.serializers import serialize
from django.http import JsonResponse
from django.shortcuts import render
from django.views import View

from .models import TopSongPoularity

def index(request):
    return render(request, "index.html", {})

class ListTopSongs(View):
    # set default page limit as 10
    page_limit = 10 # default

    '''
    Helper method to get the pagination context
    out of queryset of given page number with limit.
    Args: 
        queryset: Filtered queryset object
        page: a number representing the page number
        limit: the result count, per page.

    Returns the JSON of queryset for the given page, 
        with pagination meta info.
    '''
    def get_paginated_context(self, queryset, page, limit):
        if not page:    page = 1 # if no page provided, set 1

        # if limit specified, set the page limit
        if limit:   
            self.page_limit = limit  

        # instantiate the paginator object with queryset and page limit
        paginator = Paginator(queryset, self.page_limit)
        # get the page object
        page_obj = paginator.get_page(page)
        # serialize the objects to json
        serialized_page = serialize("json", page_obj.object_list)
        # get only required fields from the serialized_page json.
        serialized_page = [obj["fields"] for obj in json.loads(serialized_page)]

        # return the context.
        return {
            "data": serialized_page,
            "pagination": {
                "page": page,
                "limit": limit,
                "has_next": page_obj.has_next(),
                "has_prev": page_obj.has_previous(),
                "total": queryset.count()
            }
        }

    '''
    GET method for this View.
    '''
    def get(self, request, *args, **kwargs):
        # fetch the query params
        page = request.GET.get('page')
        limit = request.GET.get('limit')
        country = request.GET.get('country')
        start = request.GET.get('start')
        end = request.GET.get('end')

        sort_by = request.GET.get('sort_by')
        # get all results from DB.
        queryset = TopSongPoularity.objects.all()

        '''filter the queryset object based on query params'''
        # 1. on basis of country
        if country and country != "all":
            queryset = queryset.filter(country=country)
        # 2. On basis of date (start and end date)
        if start and end:
            if start != "0" and end != "0":
                queryset = queryset.filter(
                    year__gte = start, 
                    year__lte = end
                )

        # 3. Sorting the filtered queryset
        if sort_by and sort_by != "0":
            queryset = queryset.order_by(sort_by)

        # return the serialized output by 
        # calling method 'get_paginated_context'
        to_return = self.get_paginated_context(queryset, page, limit)
        return JsonResponse(to_return, status = 200)

def getCountries(request):
    # get Countries from the database 
    # excluding null and blank values
    if request.method == "GET" and request.is_ajax():
        country = TopSongPoularity.objects.all().\
                values_list('country').distinct()
        country = [c[0] for c in list(country)]

        return JsonResponse({
            "country": country, 
        }, status = 200)

Creating URLs

Now, let’s route the views:

#my_proj/urls.py
from django.urls import path
from my_app.views import ListTopSongs, index, getCountries

urlpatterns = [
    path('api/get/top_songs', ListTopSongs.as_view()),
    path('api/get/countries', getCountries, name = "get_countries"),
    path('', index)
]

Creating templates

Now that the backend code is done, let’s move to the frontend.

I have used a base template(base.html) as follows, which includes Bootstrap and jQuery libraries:

<!--templates/base.html-->
<!--doctype HTML-->
<html>
<head>
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta property="og:locale" content="en_US" />
    <meta charset="utf-8" />
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1">

    <title>Log rocket</title>
    <!-- css cdn includes -->
    <link rel="stylesheet" href = "https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
    {% block style %}
    {% endblock style %}
</head>

<body>
    {% block content %}
    {% endblock %}
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.1.1/jquery.min.js"></script>
    <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.5.2/js/bootstrap.min.js"></script>
    {% block javascript %}
    {% endblock javascript %}
</body>

</html>

Now let’s create the index.html which displays the table with the filters. This template file inherits the base.html and creates the table with a header and empty body. In the end, it also contains two buttons for “Next” and “Previous.”

The remaining part of the index.html, which is the JavaScript part, is explained below:

<!--templates/index.html-->
{% extends 'base.html' %}

{% block content %}
<section>
    <div class="container-fluid">
        <div class="row">
            <div class="col-sm-2 col-2">
                <div class="form-group">
                    <label for="country">Country</label>
                    <select class="form-control" id="countries" url={% url 'get_countries' %}>
                    </select>
                </div>
            </div>
            <div class="col-sm-2 col-2">
                <div class="form-group">
                    <label for="year">Year</label>
                    <select class="form-control" id="year">
                        <option value="0" start=0 end=0>All years</option>
                        <option value="1" start=2019 end=2020>2019-2020</option>
                        <option value="2" start=2018 end=2019>2018-2019</option>
                        <option value="3" start=2016 end=2018>2016-2018</option>
                        <option value="4" start=2010 end=2016>2010-2016</option>
                        <option value="5" start=1900 end=2010>1900-2010</option>
                    </select>
                </div>
            </div>

            <div class="col-sm-2 col-2">
                <div class="form-group">
                    <label for="sort">Sort By</label>
                    <select class="form-control" id="sort">
                        <option value="0">No option selected</option>
                        <option value="duration">Duration</option>
                        <option value="pop">Pop</option>
                        <option value="year">Year</option>
                    </select>
                </div>
            </div>
        </div>
    </div>
</section>

<section>
    <div class="container">
        <div class="row justify-content-center table-responsive">
            <div id="result-count" class="text-right">
                <span class='font-weight-bold'></span> results found.
            </div>
            <div id="page-count" class="text-right">Page:
                <span class='font-weight-bold'></span>
            </div>

            <table class="table table-light table-bordered table-hover" id="hero_table" data-toggle="table">
                <thead class="thead-dark">
                    <tr>
                        <th data-field="title">Title</th>
                        <th data-field="country">Country</th>
                        <th data-field="top_genre">Top Genre</th>
                        <th data-field="artist">Artist</th>
                        <th data-field="duration">Duration</th>
                        <th data-field="pop">Pop</th>
                        <th data-field="year">Year</th>
                    </tr>
                </thead>
                <tbody id="table_body">
                </tbody>
            </table>
        </div>
        <div class="row justify-content-center">
            <nav aria-label="navigation">
                <ul class="pagination">
                    <li class="page-item">
                        <button class="btn btn-primary page-link" id = "previous">Previous</button>
                    </li>
                    <li class="page-item pull-right">
                        <button class="btn btn-primary page-link" id="next">Next</button>
                    </li>
                </ul>
            </nav>
        </div>
    </div>
</section>
{% endblock content %}

Creating client-side scripting

The last part of this guide is to connect the frontend with the backend using AJAX. Refer to the comments mentioned in the code snippets below:

<!---templates/index.html--->
{% block javascript %}
<script>
    // maintaining the state of each variable.
    var current_page = 1; // maintains the current page
    var page_limit = 10; // the limit of results shown on page.
    var sort_by = ""; // maintains the select option for sort_by
    var country = ""; // maintains the select option for country
    var start_year = ""; // maintains the select option for start_yr
    var end_year = ""; // maintains the select option for end_yr

    function get_list_url(page) {
        // returns the consructed url with query params.
        return `api/get/top_songs?page=${page}&limit=${page_limit}&country=${country}&sort_by=${sort_by}&start=${start_year}&end=${end_year}`;
    }

    function getCountries() {
        // call the ajax and populates the country select options
        $.ajax({
            method: 'GET',
            url: $("#countries").attr("url"),
            success: function (response) {
                countries_option = "<option value='all' selected>All Countries</option>";
                $.each(response["country"], function (a, b) {
                    countries_option += "<option>" + b + "</option>"
                });
                $("#countries").html(countries_option)
            },
            error: function (response) {
                console.log(response)
            }
        });
    }

    // On select change of the country select, call the getAPIData
    $("#countries").on("change", function (e) {
        current_page = 1;
        country = this.value
        getAPIData(get_list_url(current_page));
    });
    // On select change of the year select, call the getAPIData
    $("#year").on("change", function (e) {
        current_page = 1;
        start_year = $(this).find(':selected').attr("start");
        end_year = $(this).find(':selected').attr("end");
        getAPIData(get_list_url(current_page));
    })
    // On select change of the sort select, call the getAPIData with sortby.
    $("#sort").on("change", function (e) {
        current_page = 1;
        sort_by = this.value
        getAPIData(get_list_url(current_page));
    })

    // Helper method that popluates the html table with next and prev
    // url, and current page number.
    function putTableData(response) {
        // creating table row for each response and
        // pushing to the html cntent of table body of table_body table
        let row;
        $("#table_body").html("");
        if (response["data"].length > 0) {
            $.each(response["data"], function (a, b) {
                row = "<tr> <td>" + b.title + "</td>" +
                    "<td>" + b.country + "</td>" +
                    "<td>" + b.top_genre + "</td>" +
                    "<td>" + b.artist + "</td>" +
                    "<td>" + b.duration + "</td>" +
                    "<td>" + b.pop + "</td>" +
                    "<td>" + b.year + "</td>" +
                    $("#table_body").append(row);
            });
        }
        else{
            // if there is no results found!
           $("#table_body").html("No results found."); 
        }
        if (response.pagination.has_prev) {
            // sets the previous page url.
            $("#previous").attr("data-url", get_list_url(current_page - 1));
            $("#previous").attr("disabled", false);
        } else {
            // if there is no prev page available, disable the btn.
            $("#previous").attr("disabled", true);
        }
        if (response.pagination.has_next) {
            // sets the next page url.
            $("#next").attr("data-url", get_list_url(current_page + 1));
            $("#next").attr("disabled", false);
        } else {
            // if there is no next page available, disable the btn.
            $("#next").attr("disabled", true)
        }
    }

    // On click of next/prev button, call the getAPIData with the given url.
    $(".page-link").click(function (e) {
        e.preventDefault();
        let url = $(this).attr("data-url");
        getAPIData(url);
    })

    // Main method which calls AJAX to get the data from backend.
    function getAPIData(url) {
        $.ajax({
            method: 'GET',
            url: url,
            success: function (response) {
                current_page = parseInt(response.pagination.page)
                putTableData(response);
                // put the total result count.
                $("#result-count span").html(response.pagination.total)
                $("#page-count span").html(response.pagination.page)
            },
            error: function (response) {
                $("#hero_table").hide();
            }
        });
    }

    //on page load, call this two methods.
    getAPIData(get_list_url(current_page));
    getCountries()
</script>
{% endblock javascript %}

Conclusion

In this guide, you have learned how to use AJAX and how to communicate with the backend asynchronously. Filtering the tabular data is a common scenario to handle, and I hope this guide gave you a better understanding of how to handle filtering the data.

You can also use REST frameworks such as Django REST framework to keep things simple, if you prefer.

If you face any issues during following the guide, you can always check my Github Repository to view the whole project.