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

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

    
    
    
    
    
    

    
    
    
    {% block style %}
    {% endblock style %}



    {% block content %}
    {% endblock %}
    
    
    {% block javascript %}
    {% endblock javascript %}

{% extends 'base.html' %}

{% block content %}

    

        

            

                

                    Country
                    
                    
                
            
            

                

                    Year
                    
                        All years
                        2019-2020
                        2018-2019
                        2016-2018
                        2010-2016
                        1900-2010
                    
                
            

            

                

                    Sort By
                    
                        No option selected
                        Duration
                        Pop
                        Year
                    
                
            
        
    



    

        

            

                 results found.
            
            
Page:
                
            

            

                

                    

                        
Title
                        
Country
                        
Top Genre
                        
Artist
                        
Duration
                        
Pop
                        
Year
                    
                
                

                
            
        
        

            
        
    

{% endblock content %}

{% block javascript %}

{% endblock javascript %}