Prefetch query with relational conditions - Django

I'm trying to make a request that prefetch all the data to reduce the amount of requests to my database. I'm using the prefetch_related() method and the Prefetch object class to tell what to load in the same request.

Code:

pairs_queryset = Pair.objects.filter(quote_id="2781", last_price__isnull=False) \
                 | Pair.objects.filter(quote_id="825", last_price__isnull=False) \
                 | Pair.objects.filter(quote_id="3408", last_price__isnull=False)

queryset = Portfolio.objects.prefetch_related(
    Prefetch('connections__wallets__currency__metadata__images'),
    Prefetch('connections__wallets__currency__base_pairs', queryset=pairs_queryset),
).get(id=portfolio_id)

data = Serializer(queryset).data

Models:

class Connection(models.Model):
    exchange = models.ForeignKey(Exchange, related_name='exchange', on_delete=models.CASCADE)

    portfolios = models.ManyToManyField(Portfolio, related_name='connections')

    # ...

class Pair(models.Model):
    id = models.IntegerField(primary_key=True)  # CoinMarketCap id

    exchange = models.ForeignKey(Exchange, related_name='pairs', on_delete=models.CASCADE)

    # ...

I have multiple base_pairs in my database that have a relation on to an Exchange(see above).

My issue: I would like to add a filter to the prefetch related objects that uses a value contained in the connection (i.e. exchange_id) of the prefetch like this:

connections__wallets__currency__base_pairs
     ^                               |
     |_______________________________|
          depends on the current 
    connection's exchange_id attribute 

As connections is a set (multiple values), I want that the following: connections__wallets__currency__base_pair retrieve the base_pairs related to the connection currently fetched.

I read the documentation and couldn't find any solution. Using prefetch highly decreases the request time (~5s instead of ~3min).

Another way:

# get portfolio + prefetch connections (and other things...)
    portfolio = Portfolio.objects.prefetch_related(
        'connections__wallets__currency__metadata__images'
    ).get(id=portfolio_id)

    for connection in portfolio.connections.all():
        # build filter
        pairs_queryset = ExchangePair.objects.filter(
            exchange_id=connection.exchange_id,
            quote_id__in=["825", "2781", "3408", "4687"],
            last_price__isnull=False,
        )
        # prefetch for related connections
        prefetch_related_objects(
            [connection],
            Prefetch('wallets__currency__base_pairs', queryset=pairs_queryset),
        )

Here, Django is reusing cache from the previous connections queries of the loop. So it is very fast. However, the cached base_pairs are mixed in the results because Django forgets about the filter queryset I pass to the Prefetch() instance. It clearly seems to be a bug with Django. So I'm looking for another way to achieve this.

I don't know if what you ask exactly is possible, but you may split the prefetch stage in three steps:

• prefetch the connections
• build a filter based on the attributes of the connections objects
• prefetch the data linked to the connections using prefetch_related_objects https://docs.djangoproject.com/en/3.2/ref/models/querysets/#django.db.models.prefetch_related_objects

# get portfolio + prefetch connections (and other things...)
portfolio = Portfolio.objects.prefetch_related(
    'connections__wallets__currency__metadata__images'
).get(id=portfolio_id)

# build filter
pairs_queryset = Pair.objects.filter(
    quote_id__in=("2781","825","3408"),
    last_price__isnull=False,
    connection__zzz__in = set(_.zzz for _ in portfolio.connections.all())
)

# prefetch for related connections
prefetch_related_objects(
    portfolio.connections.all(),
    Prefetch('wallets__currency__base_pairs', queryset=pairs_queryset
)
data = Serializer(portfolio).data