Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Django select_related in reverse

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.

like image 768
darkpool Avatar asked Jun 06 '16 08:06

darkpool


1 Answers

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

like image 174
darkpool Avatar answered Oct 05 '22 22:10

darkpool