Django ORM中的分组？

Question

我有两种型号

class ShareType(models.Model):
    code = models.CharField(max_length=10, null=False, blank=False)
    type = models.CharField(max_length=56, null=False, blank=False)

class ShareAllocation(models.Model):
    session_key = models.CharField(max_length=40, null=True)
    share_type = models.ForeignKey(ShareType, on_delete=models.CASCADE, null=True)
    share_holder = models.ForeignKey(ShareHolder, on_delete=models.CASCADE, null=True)
    number_of_shares = models.IntegerField(null=False, blank=False)
    amount_paid = models.IntegerField(null=False, blank=False)
    amount_unpaid = models.IntegerField(null=False, blank=False)
    is_beneficial_owner = models.BooleanField(null=False, blank=False)

我使用的是这个查询，这显然是不正确的

share_structure = ShareType.objects.annotate(Sum('shareallocation__number_of_shares')).\
                filter(shareallocation__session_key=self.storage.request.session.session_key)

shareallocation表的示例数据

12,5,2,1,false,9,9,098fiy6m9f0tkkmnk8jr715pyjlzcai6
13,10,2,1,false,10,12,098fiy6m9f0tkkmnk8jr715pyjlzcai6
14,15,2,1,false,12,12,098fiy6m9f0tkkmnk8jr715pyjlzcai6

共享类型表的示例数据

9,ORD,(ORD) Ordinary 
10,A,(A) A 
11,B,(B) B 
12,MAN,(MAN) Management 
13,LG,(LG) Life Governors 
14,EMP,(EMP) Employees 
15,FOU,(FOU) Founders 
16,PRF,(PRF) Preference 
17,CUMP,(CUMP) Cumulative Preference 
18,NCP,(NCP) Non Cumulative Preference 
19,REDP,(REDP) Redeemable Preference 
20,NRP,(NRP) Non Redeemable Preference 
21,NCRP,(NCRP) Non Cum. Redeemable Preference 
22,PARP,(PARP) Participative Preference 
23,RED,(RED) Redeemable 
24,INI,(INI) Initial 
25,SPE,(SPE) Special  

所需输出：

• 代码
• 股份数量（总数）
• 已付金额（合计）
• 未付金额（合计）

Answer 1

我想这就是所谓的改变！我有一种感觉，我会看到很多东西……放在一边

这是解决办法

    share_structure = ShareAllocation.objects.values('share_type__code','share_type__type').\
        annotate(share_count=Sum('share_type')). \
        annotate(number_of_shares=Sum('number_of_shares')).\
        annotate(amount_paid=Sum('amount_paid')).\
        annotate(amount_unpaid=Sum('amount_unpaid'))

挑战在于理解或不理解我所有的列名都来自values字段

从本练习中学到的主要内容是，引用表的列将作为tablename\uuu filedname的值进入 和汇总列将来自注释函数

Answer 2

更简洁的方法是在ShareType模型中加入一个函数，用于计算股票数量、支付金额和未支付金额

from django.db.models import Sum

class ShareType(models.Model):
    code = models.CharField(max_length=10, null=False, blank=False)
    type = models.CharField(max_length=56, null=False, blank=False)

    def number_of_shares(self):
        return ShareAllocation.objects.filter(share_type = self).count()
    
    def amount_paid(self):
        return ShareAllocation.objects.filter(share_type = self).aggregate(Sum('amount_paid'))

    def amount_unpaid(self):
        return ShareAllocation.objects.filter(share_type = self).aggregate(Sum('amount_unpaid')) 

class ShareAllocation(models.Model):
    session_key = models.CharField(max_length=40, null=True)
    share_type = models.ForeignKey(ShareType, on_delete=models.CASCADE, null=True)
    share_holder = models.ForeignKey(ShareHolder, on_delete=models.CASCADE, null=True)
    number_of_shares = models.IntegerField(null=False, blank=False)
    amount_paid = models.IntegerField(null=False, blank=False)
    amount_unpaid = models.IntegerField(null=False, blank=False)
    is_beneficial_owner = models.BooleanField(null=False, blank=False)

然后，您可以像读取字段一样读取这些值