[Answered ]-Django multiple table query – convert MySQL to Django (Python) query

by

1👍

✅

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']
            })
👤Nova

0👍

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

Template

        <tbody>
            {% for report in queryset %}
            <tr style="background-color:#ffffff";>
                <td>{{ report.account__name }}</td>
                <td>{{ report.wallet_total |default_if_none:0 }}</td>
                <td>{{ report.expense_total |default_if_none:0 }}</td>
                <td>{{ report.remain_total |default_if_none:0 }}</td>
            </tr>
            {% endfor %}
        </tbody>
👤APorto

Leave a comment