CODEWITHSUNDEEP
pythonmysqldjango
Manish Ojha
Manish Ojha

Reputation: 515

How to join two mysql tables in my Django application

I have two tables in my MySQL database:

First Table: For Subscribers (id, name, area, subscriberNumber, phoneNumber)

Second Table: For Monthly Payment (id, subscriberNumber, month, year, amount, discount, fine, billNumber)

I have a HTML page where the list of the Subscriber. Whenever the admin clicks on one of the subscriber the detail of the user is shown.But currently I can only show the user detail(from the first table). I want the page to show the list of payments he/she has done throughout the year(from the second table). I want to join the tables and show the details of both the table in one HTML page.

This is the code to display the data of the first table in views.py

@login_required
def userDetail(request, id=None):
    instance = get_object_or_404(Subscribers.objects.using('db2'), id=id)
    context = {
        "Name": instance.name,
        "instance": instance,
    }
    return render(request, 'userDetail.html', context)

How do I show the payments done by the user on the same page as their details are in?

Models.py

from __future__ import unicode_literals
from django.db import models
from django.core.urlresolvers import reverse

class Subscribers(models.Model):
    name = models.CharField(max_length=120)
    area = models.CharField(max_length=30)
    phoneNumber = models.CharField(max_length=10)
    installationCost = models.CharField(max_length=10)
    subsNumber = models.CharField(max_length=100)

    def get_absolute_url(self):
        return reverse("userDetail", kwargs={"id": self.id})


class Payments(models.Model):
    month = models.CharField(max_length=120)
    year = models.CharField(max_length=4)
    amount = models.CharField(max_length=10)
    discount = models.CharField(max_length=10)
    fine = models.CharField(max_length=10)
    billNumber = models.CharField(max_length=100)
    subsNumber = models.CharField(max_length=100)

    def get_absolute_url(self):
        return reverse("userDetail", kwargs={"id": self.id})

Upvotes: 2

Views: 849

Answers (2)

Arun
Arun

Reputation: 2003

To show the payment of a user you can use:

instance = get_object_or_404(Subscribers.objects.using('db2'), id=id)
try:    
    payment_info = Payments.objects.using('db2').get(subsNumber=instance.subsNumber)
except Payments.DoesNotExist:
    payment_info = None
context = {
    "Name": instance.name,
    "instance": instance,
    "payment": payment_info
}

Upvotes: 2

Exprator
Exprator

Reputation: 27503

why to use to much of confusion??

do this instead 

instance = Subscribers.objects.using('db2').get(id=id)
payment_info = Payments.objects.using('db2').get(subsNumber=instance.subsNumber)
context = {
    "Name": instance.name,
    "instance": instance,
    "payment": payment_info
}

Upvotes: 1

Related Questions