CODEWITHSUNDEEP
djangojoindjango-rest-framework
ianbroad
ianbroad

Reputation: 111

How to join 3 models of existing database tables with specific column key?

I'm developing a web app using Django REST as the backend, and Angular as the frontend.

I have a legacy database (read-only) that has 3 tables that I want to join together to get all the data I need.

Basically, I'm using the result of this query to auto-populate a form. The user will enter a known meter number into the form and then click a button that will pull the other related information into the form - minimizing data entry.

Simplified database tables and fields

MeterNumber

Customer

ServiceLocation

So, as you can see, these tables can all join to the MeterNumber table based on the bi_acct and bi_srv_loc_nbr fields. I want to be able to make a REST call to something like:

server/meter_number/123456

And then the JSON response would be something like:

{
    "bi_acct": 111111,
    "bi_srv_loc_nbr": 222222,
    "bi_mtr_nbr": 123456,
    "customer": [
        {
            "bi_acct": 111111,
            "needed_field": "... "
        }
    ],
    "service_location": [
        {
            "bi_srv_loc_nbr": 222222,
            "needed_field": "..."
        }
    ]
}

And this response would be used to auto-populate the form.

But how do I create these joins and have the data in the JSON response? Can I do it using serializers?

Models:

class MeterNumber(models.Model):

    bi_acct = models.IntegerField(
        db_column='BI_ACCT')
    bi_srv_loc_nbr = models.IntegerField(
        db_column='BI_SRV_LOC_NBR'
    bi_mtr_nbr = models.IntegerField(
        db_column='BI_MTR_NBR', blank=True, null=True)

    class Meta:
        app_label = 'cis'
        db_table = 'MeterNumber'
        managed = False

    def __str__(self):
        return str(self.bi_acct)


class Customer(models.Model):

    bi_acct = models.IntegerField(
        db_column='BI_ACCT')

    other_fields = ... 

    class Meta:
        app_label = 'cis'
        db_table = 'Customer'
        managed = False

    def __str__(self):
        return str(self.bi_acct)


class ServiceLocation(models.Model):

    bi_srv_loc_nbr = models.IntegerField(
        db_column='BI_SRV_LOC_NBR')

    other_fields = ... 

    class Meta:
        app_label = 'cis'
        db_table = 'ServiceLocation'
        managed = False

    def __str__(self):
        return str(self.bi_acct)

Put more simply, given a meter number I want to join the Customer and ServiceLocation tables to the MeterNumber. This is a very simple SQL join.

Any guidance is appreciated!

Upvotes: 0

Views: 119

Answers (2)

theomeli
theomeli

Reputation: 400

I think you should use ForeignKeys, so try change MeterNumber:

class MeterNumber(models.Model):
    bi_acct = models.ForeignKey('Customer')
    bi_srv_loc_nbr = models.ForeignKey('ServiceLocation')
    bi_mtr_nbr = models.IntegerField(
        db_column='BI_MTR_NBR', blank=True, null=True
    )

Upvotes: 1

AviKKi
AviKKi

Reputation: 1204

Unfortunately best practices of ORM doesn't give flexibility of SQL, no foreign key implies no joins so you'll have to fall back to raw SQL queries, below is a example snippet. Modify the SQL query according to your need.

from django.db import connection

def my_custom_sql():
    cursor = connection.cursor()    
    cursor.execute("select * from Customer")
    row = cursor.fetchone()
    return row

Upvotes: 1

Related Questions