Django Multi Combination Model Joins

Question

Currently experiencing a problem where my models aren't joining on the foreign key properly,

Doctors can only have one toolset, essentially I need to return [DoctorName, Category, Type, Subtype, Syringe, Bandage]

ToolSetCategoryID, ToolSetTypeID, ToolSetSubTypeID all join on each other's respective tables and all join back to ToolSets

an example of the models would be:

class Doctor(models.Model):
    DoctorID = models.AutoField(db_column='DoctorID', primary_key=True)
    ToolSetID = models.ForeignKey("ToolSets", db_column='ToolSetID', on_delete=models.CASCADE)
    DoctorName = models.CharField(db_column='DoctorName', max_length=100)

    class Meta:
        managed = False
        db_table = 'tblDoctors'
        default_permissions = ['add','edit','delete']

class ToolSetCategories(models.Model):
    ToolSetCategoryID = models.AutoField(db_column='ToolSetCategoryID', primary_key=True)
    Category = models.CharField(db_column='Category', max_length=100)

    class Meta:
        managed = False
        db_table = 'tblToolSetCategories'
        default_permissions = ['add','edit','delete']

class ToolSetTypes(models.Model):
    ToolSetTypeID = models.AutoField(db_column='ToolSetTypeID', primary_key=True)
    ToolSetCategoryID = models.ForeignKey("ToolSetCategories",db_column='ToolSetCategoryID',on_delete=models.CASCADE)
    Type = models.CharField(db_column='Type', max_length=100)

    class Meta:
        managed = False
        db_table = 'tblToolSetTypes'
        default_permissions = ['add','edit','delete']

class ToolSetSubTypes(models.Model):
    ToolSetSubTypeID = models.AutoField(db_column='ToolSetSubTypeID', primary_key=True)
    ToolSetTypeID = models.ForeignKey("ToolSetTypes",db_column='ToolSetTypeID',on_delete=models.CASCADE)
    ToolSetCategoryID = models.ForeignKey("ToolSetCategories",db_column='ToolSetCategoryID',on_delete=models.CASCADE)
    SubType = models.CharField(db_column='SubType', max_length=100)
    
    class Meta:
        managed = False
        db_table = 'tblToolSetSubTypes'
        default_permissions = ['add','edit','delete']

class ToolSets(models.Model):
    ToolSetID = models.AutoField(db_column='ToolSetID', primary_key=True)
    
    ToolSetCategoryID = models.ForeignKey("ToolSetCategories",db_column='ToolSetCategoryID',on_delete=models.CASCADE)
    ToolSetTypeID = models.ForeignKey("ToolSetTypes",db_column='ToolSetTypeID',on_delete=models.CASCADE)
    ToolSetSubTypeID = models.ForeignKey("ToolSetSubTypes",db_column='ToolSetSubTypeID',on_delete=models.CASCADE)
    
    Syringe = models.CharField(db_column='Syringe', max_length=100)
    Bandage = models.CharField(db_column='Bandage', max_length=100)

    class Meta:
        managed = False
        db_table = 'tblToolSets'
        default_permissions = ['add','edit','delete']

Answer 1

I've found either the fix or a workaround.

I had to filter the conditions I would've used in a join instead of using a join.

You can use Django's built in function F in order to reference a fields dynamic data to filter against (such as filtering field data against another field's data).

Not the best looking solution but is working for now, if anyone can find a more efficient way of doing this please post an answer below.

from django.db.models import F
    Doctor.objects.using("test").filter(DoctorID=DoctorID).filter(
    ToolSetID__ToolSetTypeID__ToolSetCategoryID=F("ToolSetID__ToolSetCategoryID"),          
    ToolSetID__ToolSetSubTypeID__ToolSetTypeID=F("ToolSetID__ToolSetTypeID"),            
    ToolSetID__ToolSetSubTypeID__ToolSetCategoryID=F("ToolSetID__ToolSetCategoryID"),
)