我有一个带有Django Rest Framework的Django应用程序,该应用程序将记录存储在Postgres中。
Vehicle
模型具有一个end_date
DateField
(代表财务协议的最终付款日期)和一个monthly_payment
FloatField
(代表付款金额)。财务付款是每月一次,即每月的最后一天(例如end_date
为2020年1月25日),则从现在到25/01/9之间的每月25号进行付款。 2020年。)
我有一个ListVehicle
ListCreateAPIView
返回了车辆记录的分页列表。
我正在使用自定义PageNumberPagination
类返回data
数组旁边的results
对象,该数组是通过汇总Vehicle
模型中的某些字段而填充的。
我想在此data
对象中包含一个字段,其中包含要在数据库中所有Vehicle
实体上支付的剩余总金额。
我尝试使用模型中的@property
字段来计算每个Vehicle
的总剩余金额,但是您使用can't aggregate over calculated properties(至少不是使用queryset.aggregate
),所以以下解决方案不起作用:
@property
def remaining_balance(self):
return max(self.remaining_monthly_payments * self.monthly_payment,0)
@property
def remaining_monthly_payments(self):
now = datetime.datetime.now()
end = self.end_date
months = (end.year - now.year) * 12
months -= now.month + 1
months += end.month
today = now.day
final_day = end.day
if today < final_day:
months += 1
return months
我也尝试过使用分页类中的ExpressionWrapper
来首先用Vehicle
字段对每个time_remaining
进行注释,然后使用通过提取而计算出的remaining_balance
字段进行注释time_remaining
开始的月份,然后乘以monthly_payment
。然后remaining_balance
被汇总。
class VehicleFinancePagination(pagination.PageNumberPagination):
def paginate_queryset(self,queryset,request,view=None):
duration = ExpressionWrapper(F('end_date') - Now(),output_field=fields.DurationField())
queryset = queryset.annotate(duration=duration)
queryset = queryset.annotate(remaining_balance=ExpressionWrapper(ExtractMonth('duration') * F('monthly_payment'),output_field=FloatField()))
self.total_remaining_balance = queryset.aggregate(total_remaining_balance=Sum('remaining_balance'))[
"total_remaining_balance"]
return super(VehicleFinancePagination,self).paginate_queryset(queryset,view)
def get_paginated_response(self,data):
paginated_response = super(VehicleFinancePagination,self).get_paginated_response(data)
paginated_response.data['total_remaining_balance'] = self.total_remaining_balance
return paginated_response
我尝试了这种样式的注释的几种组合。 (包括在一个注释中进行整个计算)。每次返回0。如果我使用ExtractDay
而不是ExtractMonth
,我会得到一个值,所以我认为这里的问题是ExtractMonth
从DateField
获得一年的月份,但不是像我希望的那样,在DurationField
中的完整月份数,尽管this answer会建议
另一种行不通的方法是,在保存Vehicle
时将剩余余额/剩余月份存储在Vehicle
中。一旦每月付款日期过后, @mock.patch('workshop.models.vehicle.datetime')
def test_remaining_balance(self,mocked_datetime):
mocked_datetime.datetime.now.return_value = datetime.datetime(2019,11,7,1,2,3)
Vehicle.objects.create(registration="TS01 TST",monthly_payment=500,end_date=datetime.date(2020,3,1)) #2000
Vehicle.objects.create(registration="TS02 TST",monthly_payment=250,25)) #1250
Vehicle.objects.create(registration="TS03 TST",monthly_payment=400,5,1)) #2400
Vehicle.objects.create(registration="TS04 TST",monthly_payment=300,25)) #2100
Vehicle.objects.create(registration="TS03 TST",end_date=datetime.date(2018,1)) #0
Vehicle.objects.create(registration="TS04 TST",25)) #0
url = reverse('vehicles')
response = self.client.get(url)
self.assertEqual(response.status_code,status.HTTP_200_OK)
self.assertEqual(response.data['total_remaining_balance'],7750)
就会过期,直到下一次保存为止,因此汇总的总金额将不正确。
我弄错了方法吗?是否可以实现我要使用自定义数据库查询执行的操作,如果可以,那是最好的方法吗?
使用PostgreSQL 10.10,Django 2.2,DRF 3.9.4和Python 3.6
编辑: 测试用例的完整性
static String infixToPrefix(String infix){
for (int j = 0; j < infix.length(); j++){
if (infix.charAt(j) == 's' && infix.charAt(j + 1) == 'i' && infix.charAt(j + 2) == 'n'){
infix = infix.replace("sin","S");
break;
}
else if (infix.charAt(j) == 'c' && infix.charAt(j + 1) == 'o' && infix.charAt(j + 2) == 's' ){
infix = infix.replace("cos","C");
break;
}
else if (infix.charAt(j) == 't' && infix.charAt(j + 1) == 'a' && infix.charAt(j + 2) == 'n' ) {
infix = infix.replace("tan","T");
break;
}
else if (infix.charAt(j) == 'l' && infix.charAt(j + 1) == 'o' && infix.charAt(j + 2) == 'g' ) {
infix = infix.replace("log","L");
break;
}
}
Stack<String> operators = new Stack<>();
Stack<String> operands = new Stack<>();
String[] exp = infix.split("");
for (int i = 0; i < exp.length; i++) {
if (exp[i].equals("(")) {
operators.push(exp[i]);
}
else if (exp[i].equals(")")) {
while (!operators.isEmpty() && !operators.getTop().equals("(")) {
if (isMultiOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operands.getTop();
operands.pop();
String op3 = operators.getTop();
operators.pop();
String temp = op3 + op2 + op1;
operands.push(temp);
}
else if(isUnaryOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operators.getTop();
operators.pop();
String temp = op2 + op1;
operands.push(temp);
}
}
operators.pop();
}
else if (isMultiOperator(exp[i]) || isUnaryOperator(exp[i])){
while (!(operators.isEmpty()) && precedence(exp[i]) <= precedence(operators.getTop())) {
if (isMultiOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operands.getTop();
operands.pop();
String op3 = operators.getTop();
operators.pop();
String temp = op3 + op2 + op1;
operands.push(temp);
}
else if (isUnaryOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operators.getTop();
operators.pop();
String temp = op2 + op1;
}
}
operators.push(exp[i] + " ");
}
else if (Character.isLetterOrDigit(infix.charAt(i))){
boolean haveDot = exp[i].equals(".");
String temp = haveDot ? "0." : exp[i];
while ((i + 1) < exp.length && (Character.isLetterOrDigit(infix.charAt(i + 1)) || exp[i + 1].equals("."))) {
temp += exp[i + 1];
i++;
}
operands.push(temp + " ");
}
}
while (!operators.isEmpty()){
if (isMultiOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operands.getTop();
operands.pop();
String op3 = operators.getTop();
operators.pop();
String temp = op3 + op2 + op1;
operands.push(temp);
}
else if (isUnaryOperator(operators.getTop())){
String op1 = operands.getTop();
operands.pop();
String op2 = operators.getTop();
operators.pop();
String temp = op2 + op1;
operands.push(temp);
}
}
return operands.getTop() ;
}