在Django中動態地過濾查詢集的實現

django-admin startproject my_proj

cd my_proj
python manage.py startapp my_app

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

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

python manage.py shell

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

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

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

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

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

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