如何用Django的select_related重写指定SQL JOIN查询？

我正尝试在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

我的Django模型如下：

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()

但仅返回主表的字典数据，未包含关联的数据源字段（字段、表、数据库、服务器），可能我对select_related的用法理解有误？

• select_related()无参数的局限：不带参数的select_related()只会预加载直接关联的外键（即TaxonomyProcessField的data_source_field），不会自动递归加载更深层级的关联（如data_source_field关联的data_source_table，以及后续的数据库、服务器）。必须明确指定要加载的关联路径，用双下划线__表示层级关系。
• values()的默认行为：values()默认只返回主模型的字段，要获取关联模型的字段，必须显式指定这些字段的完整路径。

正确实现代码 #

# 若需要简洁键名，需先导入F对象：from django.db.models import F
result = TaxonomyProcessField.objects.filter(taxonomy_request_id=1).select_related(
    'data_source_field__data_source_table__data_source_database__data_source_server'
).values(
    'taxonomy_request_id',
    'proposed_taxonomy',
    # 直接使用路径返回，键名带层级
    'data_source_field__data_source_field_name',
    'data_source_field__data_source_table__data_source_table_name',
    'data_source_field__data_source_table__data_source_database__data_source_database_name',
    'data_source_field__data_source_table__data_source_database__data_source_server__data_source_server_name'
    # 若要简洁键名，替换为以下写法：
    # data_source_field_name=F('data_source_field__data_source_field_name'),
    # data_source_table_name=F('data_source_field__data_source_table__data_source_table_name'),
    # data_source_database_name=F('data_source_field__data_source_table__data_source_database__data_source_database_name'),
    # data_source_server_name=F('data_source_field__data_source_table__data_source_database__data_source_server__data_source_server_name')
)

关键说明 #

1. select_related指定关联路径：通过完整的层级路径告诉Django预加载所有关联对象，既避免N+1查询问题，也确保关联数据能被values()访问到。
2. values()显式指定字段：关联字段必须通过双下划线路径完整声明，否则不会被包含到返回的字典中；使用F对象可以自定义返回键名，让结果更贴合需求。

内容的提问来源于stack exchange，提问作者BigBlueMonster