Introduction to the

To build a list page that allows filtering and pagination, you have to have a few separate things working together. Django’s object-relational Mapper (ORM) and built-in paging classes make it easy for developers to increase productivity without knowing how to handle databases and SQL. In this tutorial, you’ll learn how to filter query sets dynamically using AJAX.

For the example in this article, I used a Spotify data set of the top 50 songs by country. You can also download the same data set here. As always, the code used in this tutorial is available on GitHub. You can find this link at the end of this guide.

Begin to use

To get started, start a new Django project like this.

django-admin startproject my_proj

Copy the code

Then, create a sample application.

cd my_proj
python manage.py startapp my_app

Copy the code

Update Settings. Py.

INSTALLED_APPS += [
    'my_app'
]

Copy the code

Here is the directory structure to follow in your guide.

├ ─ ─ the sqlite3 ├ ─ ─ the manage. Py ├ ─ ─ my_app / │ ├ ─ ─ just set py │ ├ ─ ─ admin. Py │ ├ ─ ─ apps. Py │ ├ ─ ─ migrations / │ ├ ─ ─ Models. Py │ ├ ─ ─ templates / │ │ ├ ─ ─ base. The HTML │ │ └ ─ ─ index. The HTML │ ├ ─ ─ tests. Py │ └ ─ ─ views. Py ├ ─ ─ my_proj / │ ├ ─ ─ Just set py │ ├ ─ ─ asgi. Py │ ├ ─ ─ Settings. Py │ ├ ─ ─ urls. Py │ └ ─ ─ wsgi. Py └ ─ ─ top50contry. CSV └ ─ ─ requirements. TXTCopy the code

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 called TopSongPoularity to store the necessary information for the dataset.

Here is the models.py for 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

Copy the code

Now that you have created the model, migrate it to the database using the following methods.

python manage.py makemigrations
python manage.py migrate

Copy the code

Next, we’re going to push all the CSV data to the database, so we’re going to execute a script with a shell.

python manage.py shell

Copy the code

Run the following script in your 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)

Copy the code

Create a view

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

GetCountries () is a function-based view (FBV) that returns 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)Copy the code

Create a URL

Now, let’s route the view.

#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)
]

Copy the code

Create a template

Now that the back-end code is complete, let’s move on to the front end.

I used a basic template (base.html), including Bootstrap and the jQuery library.

<! --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 > {% javascript block %} {% endblock javascript %} </body> </html>Copy the code

Now, let’s create index.html to display the table with filters. This template file inherits base.html and creates a table with a title and an empty body. Finally, it contains two “Next” and “Previous” buttons.

The rest of the index.html, 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 %}Copy the code

Create a client script

The final part of this tutorial is connecting the front and back ends using AJAX. Refer to the comments mentioned in the code snippet 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 %}Copy the code

conclusion

In this tutorial, you’ve learned how to use AJAX and how to communicate asynchronously with the back end. Filtering tabular data is a common processing scenario, and I hope this guide gives you a better understanding of how to process filtered data.

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

If you have any problems following this guide, you can always check out my Github repository to see the whole project.

The postFiltering QuerySets dynamically in Djangoappeared first onLogRocket Blog.