I have the following model:
class Campaign(models.Model):
some_campaign_field = models.CharField()
class Position(models.Model):
campaign = models.ForeignKey(Campaign)
some_position_field = models.CharField()
class Trade(models.Model):
position = models.ForeignKey(Position)
some_trade_field = models.CharField()
In other words, I have Campaigns which can have multiple Positions. In turn each position within the campaign can have multiple Trades.
Is there an efficient way (ie: minimal database calls) to select a Campaign along with all of its associated Positions and Trades. It doesn't look like I can use select_related
because that only works the other way, eg: for a specific Trade, select_related
will get all of the associated Positions.
Currently I am doing this in nested loops as follows:
campaigns = Campaign.objects.get()
for campaign in campaigns:
positions = campaign.position_set.all()
for position in positions:
trades = position.trade_set.all()
# do stuff
This works correctly but is very inefficient in terms of how many times the database gets hit. I there a better way to do this? Something along the lines of select_related
but in reverse? A way to do one large query to get all Campaigns along with the associated Positions and Trades without having to loop through each individually.
Thanks to the suggestions in the comments, I ended up with the following working solution:
open_campaigns = list(Campaign.objects.prefetch_related(
Prefetch('position_set',
queryset=Position.objects.all(),
to_attr='cached_positions'),
Prefetch('cached_positions__trade_set',
to_attr='cached_trades'),
).filter(exit_datetime__isnull=True))
Edit: this import is also required
from django.db.models import Prefetch
Ref. Prefetch docs
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With