使用annotate過程中遇到的報錯：
- 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")
- 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})
- 後端往前端傳數時：js中要加引号
<script>
var dt = '{{typ|safe}}'
console.log(dt)
</script>
- echarts要定義div的長寬，不然不顯示
<div class="container" id='main2' style="width: 600px;height:400px;">
</div>
<!DOCTYPE html>
<html >
<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' %}" target="_blank" rel="external nofollow" >
</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/[email protected]/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>
![TestLink導出用例轉換工具(XML2Excel)[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)