Prerequisites:
Before practicing the script of this tutorial, you have to complete the following tasks.
- Install the Django version 3+ on Ubuntu 20+ (preferably)
- Create a Django project
- Run the Django server to check the server is working properly or not.
Setup a Django app:
Run the following command to create a Django app named queryapp.
Run the following command to create the user for accessing the Django database. If you have created the user before, then you don’t need to run the command.
Add the app name in the INSTALLED_APP part of the settings.py file.
…..
'queryapp'
]
Create a folder named templates inside the queryapp folder and set the template’s location of the app in the TEMPLATES part of the settings.py file.
{
….
'DIRS': ['/home/fahmida/django_pro/queryapp/templates'],
….
},
]
Create a model for the database table:
Open the models.py file from the queryapp folder and add the following script to define the structure of products tables. Product class is defined to create a table named products with name, type, brand, and price fields. Here, name, type, and brand fields will store character data, and the price field will store the integer data.
models.py
from django.db import models
# Define class to create products table
class Product(models.Model):
name = models.CharField(max_length=100)
type = models.CharField(max_length=30)
brand = models.CharField(max_length=50)
price = models.IntegerField()
Run the makemigrations command to create a new migration based on the changes made by the models.
Run the migrate command to execute the SQL commands and create all tables in the database that are defined in the models.py file.
Modify the content of the admin.py file with the following content. Here, the models’ Product class is registered by using the register() method to display the products tables in the Django administration dashboard.
admin.py
from django.contrib import admin
# Import Product model
from .models import Product
# Register Product model
admin.site.register(Product)
Create a template file named productList.html inside the queryapp/templates/ with the following script. This script will display all data of products table in tabular form with a search box. The user will be able to search the particular records from the products table by using the search form. for loop is used in the script to iterate the data passed from the views.py file.
productList.html
<head>
<title>
Django QuerySet Tutorial
</title>
<style>
th { text-align:left; color:blue; }
table, th, td { border: 1px solid;}
h1{ color:green;}
#name{ width:350px;}
</style>
</head>
<body>
<center><h1 style="margin-left:20px;">Searching Product</h1>
<form method="get" action="">
{% csrf_token %}
Search Product: <input name="src" type="text" placeholder="Search..." value="">
</form>
</center>
<center>
<table>
<tr>
<th>ID</th><th id="name">Name</th><th>Brand</th><th>Price</th>
</tr>
{% for product in object_list %}
<tr>
<td>{{product.id}} </td> <td>{{product.name}}</td> <td>{{product.brand}}</td><td
style="text-align:right">${{product.price}}</td>
</tr>
{% endfor %}
</table>
</center>
</body>
</html>
Modify the content of the views.py file with the following script. The model and template names are defined in the ProductList class. get_queryset() method of the class is defined in the script to filter the data based on the content submitted by the search box of the template. Product.objects.all() method returns all records of the products table. request.GET.keys() method is used in the script to check any data is submitted by the search form. If this method returns true, then the request.GET.get(‘src’) method is used to check the submitted value is empty or not. If this method returns a non-empty value, then the value will be stored in the variable, keyword, and it will be used for filtering the data based on the brand and type fields from the products table.
views.py
from django.views.generic import ListView
# Import Product module
from .models import Product
# Import Q module
from django.db.models import Q
# Define class for Querying data
class ProductList(ListView):
# Define model
model = Product
# Define template
template_name = 'productList.html'
def get_queryset(self):
# Set the default query set
queryset = Product.objects.all()
# Check the form value is submitted or not
if self.request.GET.keys():
# Check the search keyword
if self.request.GET.get('src') != '':
keyword = self.request.GET.get('src')
# Set the query set based on search keyword
queryset = Product.objects.filter(Q(brand=keyword.capitalize()) | Q(type=keyword.capitalize()))
return queryset
Modify the content of the urls.py file with the following script. In the script, the ‘searchPro/’ path is defined to call the ProductList.as_view() method that will send all data and the filtered data of the products table to the template file.
urls.py
from django.contrib import admin
# Import path and include module
from django.urls import path
# Import SearchEmployee module
from queryapp.views import ProductList
urlpatterns = [
# Define the path for admin
path('admin/', admin.site.urls),
# Define the path to search product
path('searchPro/', ProductList.as_view()),
Add records into the table:
Open the Django Administration page and add some records into the products table to apply the queryset on then. Here, five records have been inserted.
All records of the products with the search box will be displayed in the browser after executing the following URL.
http://localhost:8000/searchPro
All the shampoo products displayed if the product type, ‘shampoo‘ will be searched in the search box.
The milk powder products of the Fresh brand will be displayed if the product brand, ‘fresh‘ will be searched in the search box.
Conclusion:
The way of filtering the data of a simple database table by using queryset has explained in this tutorial. The data can be filtered in different ways. The readers will understand using a queryset to filter or search data in the browser after reading this tutorial.
