3

I have a pre-made database in which there is no foreign key constraint. I was wondering if we can apply join in django ORM without foreign keys (I know its not the best practice but since its pre-made schema, I cant change it).

I looked it up but I didn't find a solution which matches my requirements. I tried the following,

https://books.agiliq.com/projects/django-orm-cookbook/en/latest/join.html

Join over django models

https://www.caktusgroup.com/blog/2009/09/28/custom-joins-with-djangos-queryjoin/

https://www.quora.com/How-do-I-join-tables-in-Django

I have table1

TId = models.BigIntegerField(primary_key=True)
    field1 = models.FloatField(null=True, blank=True, default=None)
    field2 = models.CharField(max_length=10, default=None)
    field3 = models.CharField(max_length=10, default=None)
    field4 = models.CharField(max_length=10, default=None)

and table2,

SId = models.BigIntegerField(primary_key=True)
    field5 = models.FloatField(null=True, blank=True, default=None)
    field6 = models.CharField(max_length=10, default=None)
    field7 = models.CharField(max_length=10, default=None)
    field8 = models.CharField(max_length=10, default=None)

I want the query to be,

select t1.field1, t1.field2, t2.field5 from table1 t1 inner/outer/left/right join table2 t2 on t1.TId = t2.SId 
where t2.field7 = "value";
shellbot97
  • 198
  • 1
  • 12
  • You can do join even if you don't have foreign key constraints but columns of both table should represent same data. If your `TID=1` have data about X record and `SID=1` would also have data for same X entity then you can join. First explain which type of data you have in both tables and why you are joining them. – Dark Knight Sep 11 '19 at 04:54

1 Answers1

1

Django doesn't care about foreign key constraints. You can declare your field as a foreign key anyway.

For example:

class Table2(models.Model):
    SId = models.OneToOneField('Table1', primary_key=True, db_column='SId')
    ...

Now you can do:

Table2.objects.filter(field7='value').values_list('SId__field1', 'SId__field2', 'field5')
Daniel Roseman
  • 588,541
  • 66
  • 880
  • 895