django - 如何将查询集合并为一个结果而不重复？

Question

我的型号:

class GroupBase(models.Model):
    """
    Predefined base group name
    """
    YesNo = (
        ('Yes', 'Yes'),
        ('No', 'No')
    )
    name = models.CharField(max_length=32, unique=True)
    parent = models.CharField(max_length=20)
    is_revenue = models.CharField(max_length=3, choices=YesNo, default='No')
    affects_trading = models.CharField(max_length=3, choices=YesNo, default='No')
    is_debit = models.CharField(max_length=3, choices=YesNo, default='No')

    def __str__(self):
        return self.name

class LedgerGroup(models.Model):
    """
    Ledger Group Master
    """

    group_name = models.CharField(max_length=50)
    group_base = models.ForeignKey(GroupBase, on_delete=models.DO_NOTHING, related_name='base_group', default=1)

    def __str__(self):
        return self.group_name

class LedgerMaster(models.Model):
    """
    Ledger Master
    """
    ledger_name = models.CharField(max_length=80)  # unique together with company using meta
    ledger_group = models.ForeignKey(LedgerGroup, on_delete=models.DO_NOTHING, related_name='group_ledger')
    closing_balance = models.DecimalField(default=0.00, max_digits=20, decimal_places=2)

    def __str__(self):
        return self.ledger_name

我有以下疑问:

group_debit_positive = GroupBase.objects.filter(base_group__group_ledger__company=company,is_debit__exact='Yes',base_group__group_ledger__closing_balance__gt=0).annotate(
        total_debit_positive=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0)),
        total_debit_negative=Sum(0,output_field=FloatField()),
        total_credit_positive=Sum(0,output_field=FloatField()),
        total_credit_negative=Sum(0,output_field=FloatField()))

group_debit_negative = GroupBase.objects.filter(base_group__group_ledger__company=company,is_debit__exact='Yes',base_group__group_ledger__closing_balance__lt=0).annotate(
        total_debit_positive=Sum(0,output_field=FloatField()),
        total_debit_negative=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0)),
        total_credit_positive=Sum(0,output_field=FloatField()),
        total_credit_negative=Sum(0,output_field=FloatField()))

group_credit_positive = GroupBase.objects.filter(base_group__group_ledger__company=company,is_debit__exact='No',base_group__group_ledger__closing_balance__gt=0).annotate(
        total_debit_positive=Sum(0,output_field=FloatField()),
        total_debit_negative=Sum(0,output_field=FloatField()),
        total_credit_positive=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0)),
        total_credit_negative=Sum(0,output_field=FloatField()))

group_credit_negative = GroupBase.objects.filter(base_group__group_ledger__company=company,is_debit__exact='No',base_group__group_ledger__closing_balance__lt=0).annotate(
        total_debit_positive=Sum(0,output_field=FloatField()),
        total_debit_negative=Sum(0,output_field=FloatField()),
        total_credit_positive=Sum(0,output_field=FloatField()),
        total_credit_negative=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0)))

我已经执行了所有查询的联合:

final_set = group_debit_positive.union(group_debit_negative,group_credit_positive,group_credit_negative)

我想得到一个结果，而不是在我的联合查询集中重复。

例如:

每当我尝试打印结果查询集时

for g in final_set:
        print(g.name,'-',g.total_credit_positive,'-',g.total_credit_negative)

我得到这样的结果:

Sundry Creditors - 0.0 - -213075
Purchase Accounts - 0.0 - 0.0
Sundry Creditors - 95751.72 - 0.
Sales Accounts - 844100.0 - 0.0
Sales Accounts - 0.0 - -14000.0

如您所见 Sales Account重复两次。

我想要类似以下内容:

Sundry Creditors - 0.0 - -213075
Purchase Accounts - 0.0 - 0.0
Sundry Creditors - 95751.72 - 0.
Sales Accounts - 844100.0 - -14000.0

如何停止重复的结果并使其成为单个结果。

知道任何人如何执行此操作吗？

编辑

我进一步尝试使用“|”合并查询集，它在没有重复的情况下成功合并，但它正在添加具有相同名称的结果。

我做了以下工作:

final_queryset = group_debit_positive | group_debit_negative | group_credit_positive | group_credit_negative

结果是这样的:

Sundry Creditors - -213075 - 0.0
Purchase Accounts - 0.0 - 0.0
Sundry Creditors - 95751.72 - 0.
Sales Accounts - 830100 - 0.0

它添加了结果
喜欢结果 Sales Accounts正在成为 830100(844100.0 + (-14000.0) .

谁能帮我弄清楚我做错了什么。

谢谢

Answer 1

您可以尝试使用 Case 构建一个查询集吗？和 When而不是像这样的工会:

from django.db.models import Case, When

final_set = GroupBase.objects.filter(base_group__group_ledger__company=company).annotate(
    total_debit_positive=Case(
        When(is_debit__exact='Yes', base_group__group_ledger__closing_balance__gt=0, then=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0))),
        default=Value(0),
        output_field=FloatField()
    ),
    total_debit_negative=Case(
        When(is_debit__exact='Yes', base_group__group_ledger__closing_balance__lt=0, then=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0))),
        default=Value(0),
        output_field=FloatField()
    ),
    total_credit_positive=Case(
        When(is_debit__exact='No', base_group__group_ledger__closing_balance__gt=0, then=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0))),
        default=Value(0),
        output_field=FloatField()
    ),
    total_credit_negative=Case(
        When(is_debit__exact='No', base_group__group_ledger__closing_balance__lt=0, then=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0))),
        default=Value(0),
        output_field=FloatField()
    )