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

user_9902571 0
Viewed 206

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

django-queryset django-views django
5 Answers

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

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=' - '
    )
user_68909120

.annotate()

You have to perform query on LedgerMaster model to get the preferred results to avoid repetition. use Case, When to get different values based on inline filter

from django.db.models import Case, When

final_set = LedgerMaster.objects.filter(
    company=company
).values('ledger_group__group_base__name').annotate(
    total_debit_positive=Case(
        When(ledger_group__group_base__name__is_debit__exact='Yes', 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(ledger_group__group_base__name__is_debit__exact='Yes', 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(ledger_group__group_base__name__is_debit__exact='No', 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(ledger_group__group_base__name__is_debit__exact='No', closing_balance__lt=0,
             then=Coalesce(Sum('base_group__group_ledger__closing_balance'), Value(0))),
        default=Value(0),
        output_field=FloatField()
    ),
).order_by()

output data will be like:

[
    {
        'ledger_group__group_base__name': <group_base_nameA>,
        'total_debit_positive': <amount>,
        'total_debit_negative': <amount>,
        'total_credit_positive': <amount>,
        'total_credit_negative': <amount>,
    },
    {
        'ledger_group__group_base__name': <group_base_nameB>,
        'total_debit_positive': <amount>,
        'total_debit_negative': <amount>,
        'total_credit_positive': <amount>,
        'total_credit_negative': <amount>,
    },
    ....
]
user_48591950

Thank you very much everyone, Finally I got the solution to my Question, and thought of posting it for others reference.

These are my 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_opening=Sum(0, output_field=FloatField()),
        total_debit_negative_opening=Sum(0, output_field=FloatField()),
        total_credit_positive_opening=Sum(0, output_field=FloatField()),
        total_credit_negative_opening=Sum(0, output_field=FloatField()),
        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())).exclude(name__exact='Primary').exclude(name__exact='Current Assets').values(
            'name',
            'total_debit_positive',
            'total_debit_negative',
            'total_credit_positive',
            'total_credit_negative')

    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_opening=Sum(0, output_field=FloatField()),
        total_debit_negative_opening=Sum(0, output_field=FloatField()),
        total_credit_positive_opening=Sum(0, output_field=FloatField()),
        total_credit_negative_opening=Sum(0, output_field=FloatField()),
        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())).exclude(name__exact='Primary').exclude(name__exact='Current Assets').values(
            'name',
            'total_debit_positive',
            'total_debit_negative',
            'total_credit_positive',
            'total_credit_negative')

    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_opening=Sum(0, output_field=FloatField()),
        total_debit_negative_opening=Sum(0, output_field=FloatField()),
        total_credit_positive_opening=Sum(0, output_field=FloatField()),
        total_credit_negative_opening=Sum(0, output_field=FloatField()),
        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())).exclude(name__exact='Primary').exclude(name__exact='Current Assets').values(
            'name',
            'total_debit_positive',
            'total_debit_negative',
            'total_credit_positive',
            'total_credit_negative')

    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_opening=Sum(0, output_field=FloatField()),
        total_debit_negative_opening=Sum(0, output_field=FloatField()),
        total_credit_positive_opening=Sum(0, output_field=FloatField()),
        total_credit_negative_opening=Sum(0, output_field=FloatField()),
        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))).exclude(name__exact='Primary').exclude(name__exact='Current Assets').values(
            'name',
            'total_debit_positive',
            'total_debit_negative',
            'total_credit_positive',
            'total_credit_negative')

First I return a Queryset containing dictionary.

Then I performed union among the queries, as in my Question

final_queryset = group_debit_positive.union(
        group_debit_negative, group_credit_positive, group_credit_negative, all=True).order_by('name')

Then I filter the queries Group By their name to avoid duplicate result (as annotation/aggregation cannot be performed in union querysets)

grouped_group_name = itertools.groupby(
        final_queryset, key=lambda x: (x['name']))

Then,

result = []
    for group_key, group_values in grouped_group_name:
        positive_debit = decimal.Decimal(0.0)
        negative_debit = decimal.Decimal(0.0)
        positive_credit = decimal.Decimal(0.0)
        negative_credit = decimal.Decimal(0.0)

        # Storing each value into a variable to render a list later
        for each_group in group_values:
            positive_debit += decimal.Decimal(
                each_group['total_debit_positive'])
            negative_debit += decimal.Decimal(
                each_group['total_debit_negative'])
            positive_credit += decimal.Decimal(
                each_group['total_credit_positive'])
            negative_credit += decimal.Decimal(
                each_group['total_credit_negative'])

        # Making a list of items fetched to render in Django Template
        result.append({
            'group_name': group_key,
            'positive_debit': round(positive_debit, 2),
            'negative_debit': round(negative_debit, 2),
            'positive_credit': round(positive_credit, 2),
            'negative_credit': round(negative_credit, 2),
        })

In this way I was able to get the solution to my problem.

I know Its a bit lengthy process.

Thank you every one for the help..

user_99025710
user_121653000
Related