一个相关模型的过滤器很少有过滤器预取

Question

有一个表order，它连接到表信号通过fk。我从表信号中循环循环，然后从表order访问子元素。结果，我得到n+1查询。尝试做prefetch_recated，prefetch，但没有任何帮助。

for profile in Profile.objects.all():
    for signal in profile.signals.prefetch_related("orders").all():
        filter1 = signal.orders.filter(position_direction=OrderDirectionChoice.ENTER).order_by("exchanged_at")
        filter2 = signal.orders.filter(position_direction=OrderDirectionChoice.ENTER)
        filter3 = signal.orders.filter(position_direction=OrderDirectionChoice.EXIT)
        filter4 = signal.orders.filter(exchanged_at__isnull=False)
        print(filter1, filter2, filter3, filter4)

型号

class Profile(models.Model):
    ...


class Signal(models.Model):
    profile = models.ForeignKey(Profile, related_name="signals", ...)


class Order(models.Model):
    signal = models.ForeignKey(Signal, related_name="orders", ...)
    position_direction = models.CharField(...)
    exchanged_at = models.BooleanField(...)

Answer 1

您可以使用 对象将其他属性添加到包含自定义预摘情况的返回实例，通过传递to_attr参数

for signal in profile.signals.prefetch_related(
    Prefetch('orders', queryset=Order.objects.filter(position_direction=OrderDirectionChoice.ENTER).order_by("exchanged_at"), to_attr='filter1'),
    Prefetch('orders', queryset=Order.objects.filter(position_direction=OrderDirectionChoice.ENTER), to_attr='filter2'),
    Prefetch('orders', queryset=Order.objects.filter(position_direction=OrderDirectionChoice.EXIT), to_attr='filter3'),
    Prefetch('orders', queryset=Order.objects.filter(exchanged_at__isnull=False), to_attr='filter4'),
).all():
    filter1 = signal.filter1
    filter2 = signal.filter2
    filter3 = signal.filter3
    filter4 = signal.filter4
    print(filter1, filter2, filter3, filter4)

Answer 2

如果您需要基于配置文件信号的订单列表：

signals_ids = profile.signals.values_list('id',flat=True)
profile_orders = Order.objects.filter(id__in=signals_ids)

然后，将其他过滤器应用于profile_orders varible

profile_orders = profile_orders.filter.filter（...）>

Answer 3

我认为，在您的算法中存在一个合乎逻辑的错误。
例如：Filter1，Filt2，Felter3是QuerySets，Filter4是对象
但是您仍然可以创建预取：
< noreferrer”> https://docs.djangoproject.com/en/4.0/ref/models/querysets/#django.db.models.prefetch

for signal in profile.signals.prefetch_related(
  Prefetch('filter1', queryset=Order.objects.filter(position_direction=OrderDirectionChoice.ENTER).order_by("exchanged_at")),
  Prefetch('filter2', queryset=Order.objects.filter(position_direction=OrderDirectionChoice.ENTER)),
  Prefetch('filte3', queryset= Order.objects.filter(position_direction=OrderDirectionChoice.EXIT)),
  Prefetch('filter4',queryset= Order.objects.filter(exchanged_at__isnull=False)[:1])).all():
    print(signal.filter1.all(), signal.filter2.all(), signal.filter3.all(), signal.filter4.all())