Menu
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
584
Use union operator for queryset | to take union of two queryset. If both queryset belongs to same model / single model than it is possible to combine querysets by using union operator. One other way to achieve combine operation between two queryset is to use itertools chain function. Instead of itertools.
10. Join Queries. Join can be done with select_related method: Django defines this function as Returns a QuerySet that will “follow” foreign-key relationships, selecting additional related-object data when it executes its query.
How do I combine two SQL query results? There are basically three ways to combine query data: joins, unions, and subqueries. Of those three, only unions and subqueries will combine query results. Here’s how: UNION combines distinct values from the result sets of two or more SELECT statements.
It’s important to note that the merge/combine operator | only works on querysets from the same model and before the slicing it. We were unable to load Disqus.
SUMMARY: This article discusses methods for comparing and combining multiple queries into a single result set in PostgreSQL. The following operators are covered with examples: 1. UNION 2. INTERSECT 3. EXCEPT PostgreSQL provides three set operators that allow you to compare or combine query result sets. These are UNION, INTERSECT and EXCEPT.
At this point we have two different querysets, one containing all the stories from a medium and other containing all the stories from a user using the django category. The querysets can be merged like in the example below, using the | operator: And you still can perform queryset operations:
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=' - '
)
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()
)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
