[FIXED] Conditionally bulk_create or bulk_update model instance fields based on the conditional equality of two foreign key related fields in Django

Issue

I'm trying to bulk_create or bulk_update instances of a model Matchup, where two of its fields are dependent on the equality or lack thereof of two of its related fields' reverse relationships.

The Matchup has both a home_team and away_team, both of which are ForeignKey fields. There's are also is_divisional and is_conference fields to denote whether the matchup is between teams in the same division or conference.

class Matchup(models.Model):
    home_team = models.ForeignKey(
        "teams.Team",
        on_delete=models.CASCADE,
        related_name="home_matchups",
    )
    away_team = models.ForeignKey(
        "teams.Team",
        on_delete=models.CASCADE,
        related_name="away_matchups",
    )
    is_divisional = models.BooleanField(default=False)
    is_conference = models.BooleanField(default=False)

The Team model also has two ForeignKey fields, conference and division.

class Team(models.Model):
    conference = models.ForeignKey(
        "leagues.Conference",
        on_delete=models.CASCADE,
        related_name="teams",
    )
    division = models.ForeignKey(
        "leagues.Division",
        on_delete=models.CASCADE,
        related_name="teams",
    )

So the goal here is to check whether the Matchup.home_team and Matchup.away_team belong to the same conference or division. If so, is_conference/is_divisional respectively should be True.

Here's what I initially had. It works, but leads to hundreds of duplicate queries. I perform this bulk_update after all the objects are created in bulk.

for matchup in matchup_objs:
    if matchup.home_team.division == matchup.away_team.division:
        matchup.is_divisional = True
    if matchup.home_team.conference == matchup.away_team.conference:
        matchup.is_conference = True
Matchup.objects.bulk_update(matchup_objs, ["is_divisional", "is_conference"])

In an attempt to reduce the duplicate queries, I tried using a Case() expression during the bulk_create of the objects like so.

matchup_objs = Matchup.objects.bulk_create(
    [
        Matchup(
            home_team=home_team,
            away_team=away_team,
            is_conference=Case(
                When(home_team__conference=F('away_team__conference'), then=Value(True)),
                default=Value(False)
            ),
            is_divisional=Case(
                When(home_team__division=F('away_team__conference'), then=Value(True)),
                default=Value(False)
            ),
        )
        for matchup in matchups
    ]
)

But I'm getting a FieldError: Joined field references are not permitted in this query. I believe this is because joins aren't allowed for a bulk_create operation.

I thought about using a SubQuery() here based on this answer but I'm not sure how to accomplish that as the When() statement expects a parameter name and the home_team__conference lookup is what's throwing the FieldError.

Is there a way to accomplish this or do I need to resort to raw SQL here?

Solution

if the related attributes are not cached django will hit the database again and again for every attribute you access in the loop

for matchup in matchup_objs:
if matchup.home_team.division == matchup.away_team.division:
    matchup.is_divisional = True
if matchup.home_team.conference == matchup.away_team.conference:
    matchup.is_conference = True

https://docs.djangoproject.com/en/4.1/ref/models/querysets/#django.db.models.query.QuerySet.select_related

Matchup.objects.filter(foo=foo).select_related('home_team__division', 'home_team__conference', 'away_team__division', 'away_team__conference'

Answered By - David Wenzel