Django关联查询问题：筛选指定日期赛事的联赛返回无关赛事

问题核心 #

我们有FootballLeague和EventsFootball两个Django模型，需要筛选出包含指定时间戳范围内赛事的联赛，但当前查询返回的联赛会附带所有关联赛事，而非仅保留筛选范围内的赛事。

模型定义 #

FootballLeague模型 #

class FootballLeague(UUIDModel, TimeStampedModel):
    name = models.CharField(_("Name"), max_length=255, db_index=True)
    country_name = models.CharField(
        _("Country"), max_length=255, db_index=True, null=True, blank=True
    )
    country_id = models.IntegerField(_("Country ID"), null=True, blank=True)
    

    def __str__(self):
        return self.name

    class Meta:
        verbose_name = _("Football League")
        verbose_name_plural = _("Football Leagues")
        ordering = ["name"]

EventsFootball模型 #

class EventsFootball(UUIDModel):
    event_id = models.CharField(max_length=255)
    start_time = models.IntegerField(null=True, blank=True)
    home_score_current = models.CharField(max_length=255, null=True, blank=True)
    home_score_part_1 = models.CharField(max_length=255, null=True, blank=True)
    home_score_part_2 = models.CharField(max_length=255, null=True, blank=True)
   
    home_images = models.JSONField(null=True, blank=True)
    away_score_current = models.CharField(max_length=255, null=True, blank=True)
    away_score_part_1 = models.CharField(max_length=255, null=True, blank=True)
    away_score_part_2 = models.CharField(max_length=255, null=True, blank=True)
    info_notice = models.CharField(max_length=255, null=True, blank=True)
    away_images = models.JSONField(null=True, blank=True)
    league = models.ForeignKey(
        FootballLeague, related_name="events", on_delete=models.CASCADE
    )
    start_date = models.DateField(null=True, blank=True)

    def __str__(self):
        return f"{self.league.name} - {self.round}"

问题原因 #

当前的查询代码只是筛选出存在符合条件赛事的联赛，但Django默认会返回该联赛的所有关联events数据，不会自动过滤子对象。

解决方案 #

使用Prefetch对象预筛选关联的赛事，这样既能高效查询到符合条件的联赛，又能只保留指定时间范围内的赛事。

优化后的查询代码 #

from django.db.models import Prefetch

# 定义筛选后的赛事查询集
filtered_events = EventsFootball.objects.filter(
    start_time__gte=timestamp_inicio, start_time__lte=timestamp_fin
)

# 用Prefetch关联筛选后的赛事，查询目标联赛
queryset = FootballLeague.objects.filter(
    events__in=filtered_events
).prefetch_related(
    Prefetch('events', queryset=filtered_events, to_attr='filtered_events')
)

序列化器适配（基于Django REST Framework） #

如果使用DRF，需要在序列化器中调用预筛选后的filtered_events字段，替代默认返回全部赛事的events：

class EventsFootballSerializer(serializers.ModelSerializer):
    class Meta:
        model = EventsFootball
        fields = ['start_time', 'favorite', 'event_id']

class FootballLeagueSerializer(serializers.ModelSerializer):
    events = serializers.SerializerMethodField()

    def get_events(self, obj):
        # 序列化预筛选后的赛事数据
        return EventsFootballSerializer(obj.filtered_events, many=True).data

    class Meta:
        model = FootballLeague
        fields = ['short_name', 'name', 'tournament_stage_id', 'tournament_image', 'tournament_season_id', 'template_id', 'events', 'country']

简化实现方案（适合小规模数据） #

如果数据量不大，也可以直接在序列化器中过滤赛事，无需修改查询逻辑：

class FootballLeagueSerializer(serializers.ModelSerializer):
    events = EventsFootballSerializer(many=True, read_only=True)

    def to_representation(self, instance):
        data = super().to_representation(instance)
        # 从上下文获取时间戳参数，过滤events
        timestamp_inicio = self.context['request'].query_params.get('timestamp_inicio')
        timestamp_fin = self.context['request'].query_params.get('timestamp_fin')
        data['events'] = [
            event for event in data['events']
            if int(timestamp_inicio) <= int(event['start_time']) <= int(timestamp_fin)
        ]
        return data

    class Meta:
        model = FootballLeague
        fields = ['short_name', 'name', 'tournament_stage_id', 'tournament_image', 'tournament_season_id', 'template_id', 'events', 'country']

方案对比 #

• Prefetch方案性能更优，仅执行两次数据库查询，避免N+1查询问题，适合大数据量场景。
• 序列化器过滤方案实现简单，但会先加载所有赛事再过滤，数据量大时性能会受影响。

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