Recreating JOIN query in django using select related

Question

I am trying to figure out how to recreate the following SQL join in Django:

SELECT taxonomy_request_id, proposed_taxonomy, data_source_field_name, data_source_table_name, data_source_database_name, data_source_server_name
FROM taxonomy_process_field
JOIN data_source_field dsf on dsf.id = taxonomy_process_field.data_source_field_id
JOIN data_source_table dst on dst.id = dsf.data_source_table_id
JOIN data_source_database dsd on dst.data_source_database_id = dsd.id
JOIN data_source_server dss on dss.id = dsd.data_source_server_id
WHERE taxonomy_request_id = 1

My models are:

class DataSourceServer(models.Model):
    system = models.ForeignKey('System', models.DO_NOTHING, blank=True, null=True)
    data_source_server_name = models.TextField(blank=True, null=True)
    data_source_server_description = models.TextField(blank=True, null=True)
    server_location = models.ForeignKey('ServerLocation', models.DO_NOTHING, blank=True, null=True)
    data_source_server_owner_id = models.ForeignKey(Person, models.DO_NOTHING, blank=True, null=True)
    record_last_updated = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'data_source_server'

class DataSourceDatabase(models.Model):
    data_source_server = models.ForeignKey('DataSourceServer', models.DO_NOTHING, blank=True, null=True)
    data_source_database_name = models.TextField(blank=True, null=True)
    data_source_database_description = models.TextField(blank=True, null=True)
    data_source_database_owner_id = models.ForeignKey(Person, models.DO_NOTHING, blank=True, null=True)
    record_last_updated = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'data_source_database'

class DataSourceTable(models.Model):
    data_source_database = models.ForeignKey(DataSourceDatabase, models.DO_NOTHING, blank=True, null=True)
    data_source_table_name = models.TextField(blank=True, null=True)
    data_source_table_description = models.TextField(blank=True, null=True)
    data_source_table_owner_id = models.ForeignKey(Person, models.DO_NOTHING, blank=True, null=True)
    record_last_updated = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'data_source_table'

class DataSourceField(models.Model):
    data_source_table = models.ForeignKey('DataSourceTable', models.DO_NOTHING, blank=True, null=True)
    data_source_field_name = models.TextField(blank=True, null=True)
    data_source_field_data_type = models.TextField(blank=True, null=True)
    data_source_field_data_len = models.TextField(blank=True, null=True)
    record_last_updated = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'data_source_field'

class TaxonomyProcessField(models.Model):
    taxonomy_request = models.ForeignKey('TaxonomyRequest', models.DO_NOTHING, blank=True, null=True)
    data_source_field = models.ForeignKey(DataSourceField, models.DO_NOTHING, blank=True, null=True)
    proposed_taxonomy = models.TextField(blank=True, null=True)
    recommended_taxonomy = models.TextField(blank=True, null=True)
    field_description = models.TextField(blank=True, null=True)
    record_updated_by = models.TextField(blank=True, null=True)
    record_added = models.TextField(blank=True, null=True)
    record_last_updated = models.TextField(blank=True, null=True)

    class Meta:
        managed = False
        db_table = 'taxonomy_process_field'

I have tried using the following code to get the results:

TaxonomyProcessField.objects.filter(taxonomy_request_id=1).select_related().values()

But all I get back is a dict of values without the foreign key values showing what DataSource it belongs to for field, table, database and server.

Maybe I am not understand how selected_related works?

score 0 · Answer 1 · answered Mar 28 '23 at 14:59

It seems with a little more reading, I need to give the tables that select_related() will traverse. So the correct way to use it would be:

results = TaxonomyProcessField.objects.filter(taxonomy_request_id=1).select_related(
                "data_source_field__data_source_table__data_source_database__data_source_server")

Then I can use the results as so:

for result in results:
    data_source_field = result.data_source_field
    data_source_table = data_source_field.data_source_table
    data_source_database = data_source_table.data_source_database
    data_source_server = data_source_database.data_source_server
    # Use above in further code

Recreating JOIN query in django using select related

1 Answers1