Django中select_related结合混合属性未减少查询次数的问题

我在视图中使用了select_related，整体能按预期减少查询并预取数据，但仍存在一个单独查询的情况，想确认是否能将所有查询合并为单一SQL语句。

相关代码 #

视图代码（list.py） #

class OpportunitiesList(generics.ListAPIView):
    serializer_class = OpportunityGetSerializer

    def get_queryset(self):
        queryset = Opportunity.objects.all()
        queryset = queryset.filter(deleted=False)

        return queryset.select_related('nearest_airport').order_by('id')

模型代码 #

opportunity.py

class Opportunity(models.Model):
    deleted = models.BooleanField(
        default=False,
    )
    opportunity_name = models.TextField(blank=True, null=True)
    nearest_airport = models.ForeignKey(AirportDistance,
                                        on_delete=models.SET_NULL,
                                        db_column="nearest_airport",
                                        null=True)
class AirportDistance(models.Model):
    airport_id = models.ForeignKey(Airport,
                                   on_delete=models.SET_NULL,
                                   db_column="airport_id",
                                   null=True)
    airport_distance = models.DecimalField(max_digits=16, decimal_places=4, blank=False, null=False)

    @property
    def airport_name(self):
        return self.airport_id.name

location_assets.py

class Location(models.Model):
    name = models.CharField(null=True, blank=True, max_length=255)
    location_fuzzy = models.BooleanField(
        default=False,
        help_text=
        "This field should be set to True if the `point` field was not present in the original dataset "
        "and is inferred or approximated by other fields."
    )
    location_type = models.CharField(null=True, blank=True, max_length=255)
    
    
class Airport(Location):
    description = models.CharField(null=True, blank=True, max_length=255)
    aerodrome_status = models.CharField(null=True, blank=True, max_length=255)
    aircraft_access_ind = models.CharField(null=True, blank=True, max_length=255)
    data_source = models.CharField(null=True, blank=True, max_length=255)
    data_source_year = models.CharField(null=True, blank=True, max_length=255)

    class Meta:
        ordering = ("id", )

序列化器代码 #

get.py

class OpportunityGetSerializer(serializers.ModelSerializer):
    nearest_airport = OpportunityAirportSerializer(required=False)
    
    class Meta:
        model = Opportunity
        fields = (
            "id",
            "opportunity_name",
            "nearest_airport",
        )

distance.py

class OpportunityAirportSerializer(serializers.ModelSerializer):
    class Meta:
        model = AirportDistance
        fields = ('airport_id', 'airport_distance','airport_name')

当前生成的查询语句 #

SELECT opportunity.id,
       opportunity.opportunity_name,
       opportunity.nearest_airport,
       airportdistance.id,
       airportdistance.airport_id,
       airportdistance.airport_distance
  FROM opportunity
  LEFT OUTER JOIN airportdistance
    ON (opportunity.nearest_airport = airportdistance.id)
 WHERE opportunity.deleted = false
 ORDER BY opportunity.id ASC

SELECT location.name
  FROM airport
 INNER JOIN location
    ON (airport.location_ptr_id = location.id)
 WHERE airport.location_ptr_id = 1

期望的单一查询语句 #

SELECT opportunity.id,
       opportunity.opportunity_name,
       opportunity.nearest_airport,
       airportdistance.id,
       airportdistance.airport_id,
       airportdistance.airport_distance,
       location."name" 
      FROM opportunity
      LEFT OUTER JOIN airportdistance
        ON (opportunity.nearest_airport = airportdistance.id)
      INNER join airport 
        ON (airport.location_ptr_id = airportdistance.airport_id)
      INNER join location
        ON (location.id = airport.location_ptr_id)
     WHERE opportunity.deleted = false
     ORDER BY opportunity.id ASC

解决方案 #

问题出在select_related的关联深度不够：当前只预取了Opportunity到AirportDistance的关联，但序列化时访问AirportDistance的airport_name属性会触发对Airport及其父类Location的查询，这部分关联没有被预取，导致了额外的SQL语句。

只需修改select_related，通过双下划线__链式指定多层关联：

return queryset.select_related('nearest_airport__airport_id').order_by('id')

原理说明 #

• nearest_airport__airport_id表示先关联Opportunity的nearest_airport字段（对应AirportDistance），再关联AirportDistance的airport_id字段（对应Airport）。
• 由于Airport是Location的多表继承模型，Django查询Airport时会自动JOIN对应的Location表，因此Location的name字段会被一并预取，无需额外指定关联。

修改后，Django会生成和你期望结构一致的单一SQL查询，彻底消除额外的查询请求。

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