Django multiple table query - convert MySQL to Django (Python) query-CodePudding

I need to figure out how to translate MySQL query into Django (Python) language. Any help? Basically I need to get the total from each table plus the remain total after spend.

class Trip(models.Model):
    name = models.CharField('Name', max_length=254)

class Account(models.Model):
    name = models.CharField('Name', max_length=254)    

class Wallet(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    incoming_date = models.DateField(verbose_name='Incoming Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

class Expense(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    outcome_date = models.DateField(verbose_name='Outcome Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

SELECT *, (wallet_total - expense_total) AS remain_total
FROM (
    SELECT account.name, SUM(wallet.total) AS wallet_total 
    FROM account
    INNER JOIN wallet
    ON wallet.account_id = account.id
    WHERE wallet.trip_id=4
    GROUP BY account.name
) AS wallet,
(
    SELECT account.name, SUM(expense.total) AS expense_total 
    FROM account
    INNER JOIN expense
    ON expense.account_id = account.id
    WHERE expense.trip_id=4
    GROUP BY account.name
) AS expense;

CodePudding user response：

class ReportAccount(LoginRequiredMixin, GroupRequiredMixin, AccessMixin, ListView):
paginate_by = 12
template_name = 'report_account.html'
context_object_name = 'queryset'
login_url = '/login/'
redirect_field_name = 'redirect_to'
group_required = u'users'
raise_exception = True
def get_queryset(self):
    user = get_current_user()
    userselectedtrip = SelectedTrip.objects.filter(created_by=user)
    if userselectedtrip.exists():
        trip_id = SelectedTrip.objects.get(created_by=user).trip.id
    else:
        trip_id = 1

    wallet_total = Wallet.objects.filter(trip_id=trip_id).values('account__name').annotate(wallet_total=Sum('total'))
    expense_total = Expense.objects.filter(trip_id=trip_id).values('account__name').annotate(expense_total=Sum('total'))

    qs = []
    for w in wallet_total:
        for e in expense_total:
            if w['account__name'] == e['account__name']:
                qs.append({
                    'account__name': w['account__name'],
                    'wallet_total': w['wallet_total'],
                    'expense_total': e['expense_total'],
                    'remain_total': w['wallet_total'] - e['expense_total']
                })
                break
    return qs

CodePudding user response：

If you want an explanation, comment below ^_^

from django.db.models import Sum

class Trip(models.Model):
    name = models.CharField('Name', max_length=254)

class Account(models.Model):
    name = models.CharField('Name', max_length=254)

class Wallet(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    incoming_date = models.DateField(verbose_name='Incoming Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

class Expense(models.Model):
    trip = models.ForeignKey(Trip, default=1, on_delete=models.SET_DEFAULT)
    outcome_date = models.DateField(verbose_name='Outcome Date')
    total = models.DecimalField(('Total'), max_digits=32, decimal_places=2, blank=True, null=True)
    account = models.ForeignKey(Account, default=1, on_delete=models.SET_DEFAULT)

trip_id = 4

wallet_total = Wallet.objects.filter(trip_id=trip_id).values('account__name').annotate(wallet_total=Sum('total'))
expense_total = Expense.objects.filter(trip_id=trip_id).values('account__name').annotate(expense_total=Sum('total'))

results = []
for w in wallet_total:
    for e in expense_total:
        if w['account__name'] == e['account__name']:
            results.append({
                'account__name': w['account__name'],
                'wallet_total': w['wallet_total'],
                'expense_total': e['expense_total'],
                'remain_total': w['wallet_total'] - e['expense_total']
            })