在django中使用select related重新创建JOIN查询

pjngdqdw  于 2023-03-31  发布在  Go
关注(0)|答案(1)|浏览(146)

我正在尝试如何在Django中重新创建以下SQL join:

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

我的模特是:

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'

我尝试使用以下代码来获得结果:

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

但是我得到的只是一个没有外键值的值的字典,这些值显示了它属于字段、表、数据库和服务器的数据源。
也许我不明白selected_related是如何工作的?

wfveoks0

wfveoks01#

看起来再多阅读一点,我需要给予select_related()将遍历的表。所以正确的使用方法是:

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

然后我可以这样使用结果:

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

相关问题