Django annotate报错&遇到的问题

使用annotate过程中遇到的报错：

1. Sum requires values for lower and upper bounds

views里想要实现groupby效果，结果报了这个错误。后来发现是vscode自动导入了from sympy import Sum实际应该是from django.db.models import Avg, Max, Min,Sum

dc={"region_id":1}
    
    table = models.Ytdor.objects.filter(**dc)
    
    table_s = table.values('branch_id').annotate(s_ormv=Sum("ormv")).values("branch_id","s_ormv")

2. Cannot resolve keyword ‘branch’ into field. Choices are:

报错的是以下代码：

table_s = table.values('branch').annotate(s_ormv=Sum("ormv")).values("branch","s_ormv")

因为这个表是主表，只有branch_id，外键链接到子表：
主表：
字段id：xx，branch_id:xx

子表-表名branch:
字段：id:xxx,branch:xxx

用主表字段没问题，以下代码不会报错，但是只有id，不显示外键子表的对应字段名称

table_s = table.values('branch_id').annotate(s_ormv=Sum("ormv")).values("branch_id","s_ormv")

解决办法：

遍历的方式，用外键的表filter到这个id，再返回名字

from django.http import HttpResponse
from django.shortcuts import render
from matplotlib.font_manager import json_dump

# Create your views here.
import json

#from sympy import Sum
from django.db.models import Avg, Max, Min,Sum


from app1 import forms,models


from django.views.decorators.csrf import csrf_exempt
 
@csrf_exempt
def index(request):
    br_id = request.GET.get('branch_id')
    rg_id = request.GET.get('region_id')
    ls_id = request.GET.get('lislio_id')
    km_id = request.GET.get('kam_id')
    
    if rg_id:
        if br_id:
            typ = "one_branch"
        else:
            typ = "one_region"
    else:
        if br_id:
            typ = "one_branch"
        else:
            typ = "no_select"


    print(br_id)
    print(rg_id)
    print(ls_id)
    print(km_id)
    test_ajax = {"a":1,"b":"2"}
    form = forms.fm1()
    table_all = models.Ytdor.objects.all()
    dc = {}
    if rg_id:
        dc['region_id'] = rg_id
    if br_id:
        dc['branch_id'] = br_id
    if ls_id:
        dc['lislio_id'] = ls_id
    if km_id:
        dc['kam_id'] = km_id
    

    form = forms.fm1(data = request.GET)
    
    table = models.Ytdor.objects.filter(**dc)      #筛选

    
    
    #分组聚合前一个values内写的是groupby的字段，annotate里的是聚合字段，sum，avg等，最后一个values里写的是需要呈现的字段
    #在筛选的结果下，对分公司id分组聚合，
    table_s = table.values('branch_id').annotate(s_ormv=Sum("ormv")).values("branch_id","region_id","s_ormv")
    #在筛选的结果下，对区域id分组聚合
    table_rg = table.values('region_id').annotate(s_ormv=Sum("ormv")).values("region_id","s_ormv")

    #遍历聚合后的queryset
    br_list = []
    br_ormv_list = []    
    new_dc_br = {}
    for i in list(table_s):        #遍历queryset，每一行queryset，并取指定字段
        b_id = i['branch_id']      #获取branch_id
        r_id = i['region_id']      #获取region_id
        s_orm = i["s_ormv"]        #获取求和的字段
        br_name = models.Branch.objects.filter(id=b_id).first()     #用id来对外键子表进行筛选，注意要加first
        rg_name = models.Region.objects.filter(id=r_id).first()     #类似的取region的名称

        br_list.append(str(br_name))   #要用str，不然append出来会有问题
        br_ormv_list.append(s_orm)

    new_dc_br["x_axis"] = br_list       ### 放入字典
    new_dc_br["y_axis"] = br_ormv_list  ### 放入字典
    new_dc_br["rg_id"] = r_id           ### 放入字典

    new_dc_rg = {}
    rg_list = []
    rg_ormv_list = []
    for i in list(table_rg):
        r_id = i['region_id']
        rg_name = models.Region.objects.filter(id=r_id).first()
        rg_ormv = i['s_ormv']
        rg_list.append(str(rg_name))
        rg_ormv_list.append(rg_ormv)
    new_dc_rg["x_axis"] = rg_list
    new_dc_rg["y_axis"] = rg_ormv_list
    new_dc_rg["br_id"] = r_id

    if request.method == "GET":
        return render(request,"index.html",{'table':table,'form':form,"region_id":rg_id,"branch_id":br_id,"lislio_id":ls_id,"kam_id":km_id,"typ":typ})
    else:

        
        if request.POST.get("front_to_end") == "br":
            return HttpResponse(json.dumps(new_dc_br))
        if request.POST.get("front_to_end") == "rg":
            return HttpResponse(json.dumps(new_dc_rg))              ##根据条件不同，返回不同的json.dumps

        else:
            return render(request,"index.html",{'table':table,'form':form,"region_id":rg_id,"branch_id":br_id,"lislio_id":ls_id,"kam_id":km_id,"typ":typ})

3. 后端往前端传数时：js中要加引号

    <script>
        var dt = '{{typ|safe}}'
        console.log(dt)
    </script>

4. echarts要定义div的长宽，不然不显示

<div class="container" id='main2' style="width: 600px;height:400px;">
</div>

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
    {% load static %}
    <link rel="stylesheet" href="{% static 'plugins/bootstrap-3.4.1/css/bootstrap.css' %}">
</head>

<body>

    <div class="container" id="main">
        <form method="GET">
            {% csrf_token %} {% for field in form%}<label for="exampleInputName2">{{field.label}}</label> {{field}} {% endfor%}
            <input type="submit" value="submit">
            <input type="text" name="123" id="ip1">
            <input type="button" value="submit" id="btn">
        </form>
    </div>


    <div class="container" id='main2' style="width: 600px;height:400px;">

    </div>




    <script src="{% static 'plugins/echarts.min.js'%}"></script>
    <script src="https://cdn.jsdelivr.net/npm/jquery@1.12.4/dist/jquery.min.js"></script>
    <script src="{% static 'plugins/bootstrap-3.4.1/js/bootstrap.js' %}"></script>


    <script type="text/javascript">
        var chartDom = document.getElementById('main2');
        var myChart = echarts.init(chartDom);
        var option;
    </script>




    <script>
        var dt = '{{typ|safe}}';
        console.log(dt);
        var xxx = "br";
        if (dt == "no_select") {
            xxx = "rg";
        };
    </script>



    <script>
        $('#btn').click(
            function() {
                $.ajax({
                    url: '',
                    type: 'POST',
                    data: {
                        'front_to_end': xxx
                    },
                    success: function(an_input) {
                        console.log(an_input);
                        let obj = JSON.parse(an_input);
                        $('#ip1').val(obj.x_axis);


                        /*          ##############################################          */



                        option = {
                            xAxis: {
                                type: 'category',
                                data: obj.x_axis
                            },
                            yAxis: {
                                type: 'value'
                            },
                            series: [{
                                data: obj.y_axis,
                                type: 'bar'
                            }]
                        };
                        myChart.setOption(option);
                        /*        ##########################################################     */


                    }
                })
            }
        )
    </script>


</body>

</html>