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.
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
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)
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)
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) ]
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 %}
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 %}
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.
Install LogRocket via npm or script tag. LogRocket.init()
must be called client-side, not
server-side
$ npm i --save logrocket // Code: import LogRocket from 'logrocket'; LogRocket.init('app/id');
// Add to your HTML: <script src="https://cdn.lr-ingest.com/LogRocket.min.js"></script> <script>window.LogRocket && window.LogRocket.init('app/id');</script>
Hey there, want to help make our blog better?
Join LogRocket’s Content Advisory Board. You’ll help inform the type of content we create and get access to exclusive meetups, social accreditation, and swag.
Sign up nowSOLID principles help us keep code flexible. In this article, we’ll examine all of those principles and their implementation using JavaScript.
JavaScript’s Date API has many limitations. Explore alternative libraries like Moment.js, date-fns, and the new Temporal API.
Explore use cases for using npm vs. npx such as long-term dependency management or temporary tasks and running packages on the fly.
Validating and auditing AI-generated code reduces code errors and ensures that code is compliant.
3 Replies to "Filtering QuerySets dynamically in Django"
Great article. I am only learning from you. Also following multiple author but your technique is different. I am a child in code. Still fighting for the treasure.
Nice article. Worth going through! Please keep sharing.
I really liked the concept!! I appreciate the man behind the blog