从2张表中按id、客户在表中存入的金额进行召回时出现问题

Question

我正在写一个crm，以供自己学习。

我有一个问题，因为我有一张桌子"；客户端"；在另一张表中，我存储了这个客户的存款，我需要在表中的页面上显示客户的存款；状态＝4"；，我试着把它作为公告来做，但后来我提不出相应的id。

型号.py

class Client(models.Model):
def __str__(self):
return self.name + " " + self.lastname
class Meta:
verbose_name = 'Client'
verbose_name_plural = 'Client'
name = models.CharField(max_length=64)
lastname = models.CharField(max_length=64)
phone = models.DecimalField(max_digits=20, decimal_places=0)
email = models.EmailField(max_length=64, null=True)
salesman = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='salesman')
retention = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='retention')
reg_time = models.DateField(blank=True, null=True)
class Operation(models.Model):
def __str__(self):
return self.client.name + ' ' + self.client.lastname + '  Cash: ' + str(self.cash)
client = models.ForeignKey(Client, on_delete=models.CASCADE, related_name='client')
cash = models.DecimalField(max_digits=12, decimal_places=2)
date = models.DateField(blank=True, null=True)
bank = models.ForeignKey(Bank, on_delete=models.CASCADE, related_name='bank')
status = models.ForeignKey(Status, on_delete=models.CASCADE, related_name='stat')
who = models.ForeignKey(Employee, on_delete=models.CASCADE, related_name='ftd_employees')
class Meta:
verbose_name = 'Operations'
verbose_name_plural = 'Operations'

client_list.html

<tbody>
{%  for c in clients_list %}
<tr>
<td>{{ c.id }}</td>
<td>{{ c.name }} {{ c.lastname }}</td>
<td>{{ c.email }}</td>
<td>{{ c.phone }}</td>
<td>{{ c.salesman.name }} {{ c.salesman.lastname }}</td>
<td>{{ c.retention.name }} {{ c.retention.lastname }}</td>
<td>{{ c.phone }}</td>
<td>{{ c.reg_time }}</td>
<td>{{ c.depo }}</td>
{% endfor %}
</tr>
</tbody>

views.py

@property
def client_ftd(self):
depo = Client.objects.annotate(Operation=Count("kto__id")).annotate(totals=Sum("kto__cash")).filter(kto__status=4)
return depo

def client_list(request):
employee = Employee.objects.all()
clients_list = Client.objects.all()
return render(request, 'client_list.html', {'clients_list': clients_list,
'employee': employee, 'client_ftd': client_ftd})

Answer 1

问题出在clients_list = Client.objects.all()上。

对于解决方案，您需要将.all替换为.annotate。随后，您必须用您的depo打开一个括号，因此(depo=Sum('operation__cash', filter=Q(operation__status=4)))。如您所见，使用了"Status = 4"过滤器。

通过这种方式，您将从2个表中回拨，按id，客户存款多少，在表中：(

@property
def client_ftd(self):
depo = Client.objects.annotate(Operation=Count("kto__id")).annotate(totals=Sum("kto__cash")).filter(kto__status=4)
return depo
def client_list(request):
employee = Employee.objects.all()
clients_list = Client.objects.annotate(depo=Sum('operation__cash', filter=Q(operation__status=4))) #update 
return render(request, 'client_list.html', {'clients_list': clients_list,
'employee': employee, 'client_ftd': client_ftd})

Answer 2

我假设您正在寻找这个简单的查询：

clients_list = Client.objects.annotate(depo=Sum('operation__cash', filter=Q(operation__status=4)))

看看你的代码，我认为你真的需要了解更多关于的信息

• properties(@property decorator旨在在方法的类内部使用；为什么要把它放在视图中？(
• 聚合查询(什么是"kto"，注释(Operation=Count("kto_id"((有什么意义？？(

Answer 3

我认为您可以在view.py:中使用此查询来优化数据库查询

Operation.objects.filter(status__id = 4).annotate(depo = Sum('cash')).values(
'client__id',
'client__name',
'client__email',
'client__phone',
'client__salesman__name',
'client__salesman_lastname',
'client__relation__name',
'client__relation.lastname',
'client__reg_time',
)

检查它是否有效。它应该只返回您需要的字段，并提高工作效率。避免在Python中使用循环。数据库的效率要高得多。