Prefetch with few filters for one related model-CodePudding

There is a table Order, which is connected to table Signal through FK. I loop through the records from table Signal and access the child elements from table Order. As a result, I get N 1 query. Tried to do prefetch_related, Prefetch, but nothing helps.

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)

models.py

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

CodePudding user response：

You can use Prefetch objects to add additional attributes to the returned instances that contained custom prefetched querysets by passing the to_attr argument

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)

CodePudding user response：

If you want a list of orders based on the profile Signals:

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

Then you apply the other filters on the profile_orders variable

profile_orders = profile_orders.filter(...)

CodePudding user response：

I think, in your algorithm exists a logical mistake. For example: filter1, filter2, filter3 are querysets and filter4 is object But you still can create Prefetch: 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())