How to merge queryset into a single result without any repetation?

Question

My Model:

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

I have the following queries:

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)))

I have performed union of all the queries:

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

I want to get a single result rather then getting repetation in my union queryset.

For example:

whenever I am trying to print the resulted queryset

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

I am getting results like this:

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

As you can see Sales Account is repeated twice.

I want something like the following:

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

How to stop the repetition of results and make it into a single result.

Any idea anyone how to perform this?

EDIT

I further tried using "|" to merge the queryset, it is merging successfully without repetation but it is adding the result with the same name.

I have done the following:

final_queryset = group_debit_positive | group_debit_negative | group_credit_positive | group_credit_negative

The result is coming out like this:

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

Its adding the result like the result Sales Accounts is becoming 830100(844100.0 + (-14000.0).

Can anyone help me to figure out what I am doing wrong.

Thank you

Answer 1

You can use the filter argument for Sum with a different Q object for each annotation instead. Also use the values method of the queryset to group the output by the name field, so there won't be separate entries of the same name in the output:

final_set = GroupBase.objects.filter(
    base_group__group_ledger__company=company).values('name').annotate(
    total_debit_positive=Sum('base_group__group_ledger__closing_balance', output_field=FloatField(),
        filter=Q(is_debit__exact='Yes', base_group__group_ledger__closing_balance__gt=0)),
    total_debit_negative=Sum('base_group__group_ledger__closing_balance', output_field=FloatField(),
        filter=Q(is_debit__exact='Yes', base_group__group_ledger__closing_balance__lt=0)),
    total_credit_positive=Sum('base_group__group_ledger__closing_balance', output_field=FloatField(),
        filter=Q(is_debit__exact='No', base_group__group_ledger__closing_balance__gt=0)),
    total_credit_negative=Sum('base_group__group_ledger__closing_balance', output_field=FloatField(),
        filter=Q(is_debit__exact='No', base_group__group_ledger__closing_balance__lt=0))
)
for g in final_set:
    print(
        g['name'], g['total_debit_positive'], g['total_debiit_negative'],
        g['total_credit_positive'], g['total_credit_negative'], sep=' - '
    )

Answer 2

Can you try constructing one queryset using Case and When instead of union like:

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()
    )