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