CODEWITHSUNDEEP
pythondjangodjango-models
gennad
gennad

Reputation: 5565

How can I do INNER JOIN in Django in legacy database?

Sorry for probably simple question but I'm a newby in Django and really confused.

I have an ugly legacy tables that I can not change.
It has 2 tables: 

class Salespersons(models.Model):
    id = models.IntegerField(unique=True, primary_key=True)
    xsin = models.IntegerField()
    name = models.CharField(max_length=200)
    surname = models.CharField(max_length=200)

class Store(models.Model):
    id = models.IntegerField(unique=True, primary_key=True)
    xsin = models.IntegerField()
    brand = models.CharField(max_length=200)

So I suppose I can not add Foreign keys in class definitions because they change the tables.

I need to execute such sql request: 

SELECT * FROM Salespersons, Store INNER JOIN Store ON (Salespersons.xsin = Store.xsin);

How can I achieve it using Django ORM?
Or I'm allowed to get Salespersons and Store separately i.e. 

stores = Store.objects.filter(xsin = 1000)
salespersons = Salespersons.objects.filter(xsin = 1000)

Upvotes: 2

Views: 3933

Answers (3)

emulbreh
emulbreh

Reputation: 3461

If you can make one of the xsin fields unique, you can use a ForeignKey with to_field to generate the inner join like this:

class Salespersons(models.Model):
    xsin = models.IntegerField(unique=True)

class Store(models.Model):
    xsin = models.ForeignKey(Salespersons, db_column='xsin', to_field='xsin')

>>> Store.objects.selected_related('xsin')

Upvotes: 2

Ben Jackson
Ben Jackson

Reputation: 93710

I don't see why you can't use the models.ForeignKey fields even if the database lacks the constraints -- if you don't explicitly execute the SQL to change the database then the tables won't change. If you use a ForeignKey then you can use Salespersons.objects.select_related('xsin') to request that the related objects are fetched at the same time.

Upvotes: 1

Botond Béres
Botond Béres

Reputation: 16673

Given your example query, are your tables actually named Salespersons/Store? Anyway, something like this should work:

results = Salespersons.objects.extra(tables=["Store"],
                          where=["""Salespersons.xsin = Store.xsin"""])

However, given the names of the tables/models it doesn't seem to me that an inner join would be logically correct. Unless you always have just 1 salesperson per store with same xsin.

Upvotes: 3

Related Questions