在Django中动态地过滤查询集的实现

目录

• 简介
• 开始使用
• 数据准备
• 创建视图
• 创建URL
• 创建模板
• 创建客户端脚本
• 结语

简介

要建立一个允许过滤和分页的列表页，你必须让一些独立的东西一起工作。Django的对象关系映射器（ORM）和内置的分页类使开发者在不了解如何处理数据库和SQL的情况下，也能轻松地提高工作效率。在本指南中，你将学习如何使用AJAX动态地过滤查询集。

在本文的例子中，我采用了Spotify上按国家划分的前50首歌的数据集。你也可以从这里下载同样的数据集。像往常一样，本指南中使用的代码可以在GitHub上找到。你可以在本指南的结尾处找到这个链接。

开始使用

要开始，请像这样启动一个新的Django项目。

django-admin startproject my_proj

然后，创建一个示例应用程序。

cd my_proj
python manage.py startapp my_app

更新settings.py 。

INSTALLED_APPS += [
    'my_app'
]

这里是你在指南中要遵循的目录结构。

├── 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

数据准备

在跳转到实际代码之前，我们首先需要将所有数据推送到数据库。

我已经创建了一个名为TopSongPoularity 的基本模型来存储数据集的必要信息。

下面是my_app 的models.py 。

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

现在你已经创建了模型，用下面的方法将其迁移到数据库中。

python manage.py makemigrations
python manage.py migrate

接下来，我们要把所有的CSV数据推送到数据库中，所以我们要用shell来执行一个脚本。

python manage.py shell

在shell中运行下面的脚本，将CSV数据推送到数据库中。

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

创建视图

接下来，让我们来编写视图。ListTopSongs 是一个基于类的视图（CBV），它继承了View 类。在该类的get() 方法中，它接受查询参数并相应地过滤QuerySet。在QuerySet被过滤后，它再调用get_paginated_context() ，以获得序列化格式的分页数据。

getCountries() 是一个基于函数的视图（FBV），它为数据库中所有独特的国家返回JSON输出。

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

创建URL

现在，让我们对视图进行路由。

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

创建模板

现在后端代码已经完成，让我们转到前端。

我使用了一个基本模板(base.html)，包括Bootstrap和jQuery库。

<!--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>

现在，让我们创建index.html ，显示带有过滤器的表格。这个模板文件继承了base.html ，并创建了一个带有标题和空主体的表格。最后，它还包含两个 "下一步 "和 "上一步 "的按钮。

index.html 的其余部分，即JavaScript部分，将在下面解释。

<!--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 %}

创建客户端脚本

本指南的最后一部分是使用AJAX连接前端和后端。请参考下面代码片断中提到的注释。

<!---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 %}

结语

在本指南中，你已经学会了如何使用AJAX以及如何与后端进行异步通信。过滤表格数据是一个常见的处理场景，我希望本指南能让你更好地了解如何处理过滤数据。

如果你愿意，你也可以使用REST框架，如Django REST框架来保持简单。

如果你在遵循本指南的过程中遇到任何问题，你可以随时查看我的Github仓库来查看整个项目。

到此这篇关于在Django中动态地过滤查询集的实现的文章就介绍到这了,更多相关Django动态过滤查询集内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！